SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Incorrect syntax errors
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

scottbak
Starting Member

USA
16 Posts

Posted - 11/11/2007 :  10:50:10  Show Profile  Reply with Quote
I am getting:
Server: Msg 170, Level 15, State 1, Procedure alldates, Line 1
Line 1: Incorrect syntax near 'd1'.
Server: Msg 170, Level 15, State 1, Procedure alldates, Line 4
Line 4: Incorrect syntax near 'do'.

when I try this:

drop procedure alldates;
create procedure alldates (d1 date, d2 date)
returns (thedate date)
as begin
while (d2 > d1) do
begin
thedate = d1;
d1 = d1 + 1;
suspend;
end
end;

Help! I am trying to get all the dates between a range in 2 sql columns. If there is a better way, it would be a huge help! I thought I found a solutionwith CROSS APPLY, but found it was SQL 2005!



Scott

dataguru1971
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 11/11/2007 :  11:03:05  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote
Are you trying to return actual column values or just create the dates between two ranges?

It isn't clear what you are doing, but it is clear you haven't looked at simple function or procedure creation. You must have variables passed as @d1 datetime,@d2 datetime

procedures don't need a RETURNS statement, functions do..

Again, not sure "what" it is you are trying to do this for, can you clarify what the desired result is needed to do?

See this and the output


Create procedure alldates (@d1 datetime, @d2 datetime)
as 

Create TABLE #dates (mydate datetime not null)


	Declare @days int,@start int
	Select @Days = datediff(d,@d1,@d2)
	Select @start = 0

	While @start <=@days
	Begin
	Insert Into #dates(mydate)
		Select dateadd(d,@start,@d1)
	Select @start = @start + 1
		
	end

Select * from #dates

drop table #dates
GO

Exec alldates '1/1/2007','1/15/2007'

Edited by - dataguru1971 on 11/11/2007 11:03:37
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17650 Posts

Posted - 11/11/2007 :  11:03:34  Show Profile  Reply with Quote
your procedure is all wrong
1. variable need to declare with @, eg @d1 date
2. syntax for create procedure is wrong. You are mixing function syntax with procedure. Or are you trying to create a function ?
3. T-SQL does not have while ... do
4. . . .

think you better use this instead http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519&SearchTerms=F_TABLE_DATE


KH
Time is always against us

Go to Top of Page

scottbak
Starting Member

USA
16 Posts

Posted - 11/12/2007 :  18:22:17  Show Profile  Reply with Quote
I have changed the whole query to:
USE reservations
DECLARE @myTable
TABLE (thedate smalldatetime, ag_year int, ag_month smallint, ag_day varchar(10),
ag_message varchar(10), ag_bgcolor varchar(10), ag_action varchar(10),
ag_boxit varchar(10), ag_fgcolor varchar(10), ag_bgimg varchar(10),
ag_html varchar(10))

declare @StartDate smalldatetime
declare @EndDate smalldatetime
declare @Days int
declare @CurrentDay int
declare @year int
declare @month smallint
declare @day varchar(10)
set @StartDate = (select checkin from bookings)
set @EndDate = (select checkout from bookings)
set @Days = DATEDIFF (day, @StartDate , @EndDate+1 )
set @CurrentDay = 0
while @CurrentDay < @Days
begin
insert @myTable (thedate, ag_year, ag_month, ag_day, ag_message, ag_bgcolor,
ag_action, ag_boxit, ag_fgcolor, ag_bgimg, ag_html)

values (dateadd(dd, @CurrentDay, @StartDate), year(dateadd(dd, @CurrentDay, @StartDate)),
month(dateadd(dd, @CurrentDay, @StartDate)), day(dateadd(dd, @CurrentDay, @StartDate)),
'Reserved', 'red', '', 'TRUE', '', '', '')

set @CurrentDay = @CurrentDay + 1
end

select * from @myTable

but I am getting this error:
Server: Msg 512, Level 16, State 1, Line 15
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.


how can I fix the above query to avoid the error?

Scott
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37142 Posts

Posted - 11/12/2007 :  18:27:31  Show Profile  Visit tkizer's Homepage  Reply with Quote
The problem is here:

set @StartDate = (select checkin from bookings)
set @EndDate = (select checkout from bookings)

So which value do you want to grab from bookings?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

scottbak
Starting Member

USA
16 Posts

Posted - 11/12/2007 :  18:50:32  Show Profile  Reply with Quote
I cehcked out the F_TABLE_DATE and I like that and it's fast! Good call.
But I am getting an error passing 2 columns to the start and end dates.

Here is what I have but I get errors:
declare @start datetime, @End datetime
set @Start = (select checkin from bookings)
set @End = (select checkout from bookings)
select *
from
dbo.F_TABLE_DATE (@Start,@End )
order by 1

Error:
Server: Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Server: Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Server: Msg 245, Level 16, State 1, Procedure F_TABLE_DATE, Line 723
Syntax error converting the varchar value '

*******************************************************************
* Error in function F_TABLE_DATE:
* @FIRST_DATE cannot be null
*******************************************************************

' to a column of data type int.


Scott
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37142 Posts

Posted - 11/12/2007 :  18:53:33  Show Profile  Visit tkizer's Homepage  Reply with Quote
Did you even see my post? You have to decide which value from bookings that you want to put into @Start and @End.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

scottbak
Starting Member

USA
16 Posts

Posted - 11/12/2007 :  18:54:26  Show Profile  Reply with Quote
So which value do you want to grab from bookings?

I am trying to get all the dates between checkin and checkout for each row. but as soon as I have more than 1 row it failes with that error

Scott
Go to Top of Page

scottbak
Starting Member

USA
16 Posts

Posted - 11/12/2007 :  18:55:19  Show Profile  Reply with Quote
I was typing while you must of posted. sorry I didn't see it right away. :)

Scott
Go to Top of Page

scottbak
Starting Member

USA
16 Posts

Posted - 11/12/2007 :  18:57:36  Show Profile  Reply with Quote
Here is my table:
Checkin Checkout adults children
11/30/2007 12/10/2007 2 0
12/18/2007 12/26/2007 2 0

Scott
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37142 Posts

Posted - 11/12/2007 :  18:59:11  Show Profile  Visit tkizer's Homepage  Reply with Quote
You need to add a WHERE clause to those queries so that it knows which checkin and which checkout you want.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

scottbak
Starting Member

USA
16 Posts

Posted - 11/12/2007 :  19:01:24  Show Profile  Reply with Quote
I want to pull all dates between checkin and checkout in each row and display all of the results in one.

Scott
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37142 Posts

Posted - 11/12/2007 :  19:03:45  Show Profile  Visit tkizer's Homepage  Reply with Quote
You can't do that with your current code.

Perhaps you should start over and explain your problem in words rather than with your broken T-SQL. It is much easier for us to help when we can visualize the problem with sample data provided by the OP.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

scottbak
Starting Member

USA
16 Posts

Posted - 11/12/2007 :  19:07:14  Show Profile  Reply with Quote
Sorry for the lack of clarity.
Here is what I wish to accomplsh:

I have this table:
Checkin Checkout adults children
11/30/2007 12/10/2007 2 0
12/18/2007 12/26/2007 2 0
etc..

and I wish to pull all of the dates between the checkin and checkout columns for each row and pull the combined information.
11/30/2007
12/1/2007
12/2/2007
etc... then
12/18/2007
12/19/2007
12/20/2007
etc...

Thanks for your help.

Scott
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37142 Posts

Posted - 11/12/2007 :  19:12:25  Show Profile  Visit tkizer's Homepage  Reply with Quote
Your sample data is not helping make your problem clear.

Where are you getting those dates from? Not the Bookings table data, but that other data you posted.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

dataguru1971
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 11/12/2007 :  19:15:37  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote

Are you sure you need to repeat identical information for each row for this? What is going to happen to the subsequent data set? (am presuming you will keep the reservation ID or something at each row as well?)

Perhaps there is a much simpler way to get the actual desired intent accomplished. As it stands you would have to cursor through the rows in the reservations table and pass the start and end dates through that procedure to populate this temp table. My guess is you are going to "do something" with that table once it is populated, and that there is another way to fry that fish.




Poor planning on your part does not constitute an emergency on my part.

Go to Top of Page

scottbak
Starting Member

USA
16 Posts

Posted - 11/12/2007 :  19:19:26  Show Profile  Reply with Quote
I am querying this infomation to post to a booking calendar on the web page. It will pull the dates from the table and post to the web that those days are no longer available.

Scott
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37142 Posts

Posted - 11/12/2007 :  19:21:36  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by scottbak

I am querying this infomation to post to a booking calendar on the web page. It will pull the dates from the table and post to the web that those days are no longer available.

Scott



I'm not sure how that helps us with your problem. Could you explain?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

scottbak
Starting Member

USA
16 Posts

Posted - 11/12/2007 :  19:22:29  Show Profile  Reply with Quote
the dates are coming from the people posting to the web page. all data is populated once a credit card is processed succefully. Then I need to show all the dates that are reserved on the calendar. thus the need to pull all dates in between each checkin and checkout date for each row and put to the calendar. (plus I need all the other rows listed.ag_year, ag_month, ag_day, ag_message, ag_bgcolor, ag_action, ag_boxit, ag_fgcolor, ag_bgimg, ag_html)

Scott
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37142 Posts

Posted - 11/12/2007 :  19:25:10  Show Profile  Visit tkizer's Homepage  Reply with Quote
Well hopefully someone else can help you as I don't have time to go back and forth with this. If you'd like to see how properly to post a question, check out an old post of mine: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=42516

Notice how quickly I got answers when I posted it this way. My problem was pretty complex too.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

scottbak
Starting Member

USA
16 Posts

Posted - 11/12/2007 :  19:31:32  Show Profile  Reply with Quote
All I need is the dates between the checkin and checkout columns in the table. What else do you need to understand? I have a query listed above that pulls fine for one column but not for more than one column. someone post above the use of F_TABLE_DATE and I installed that and the problem I have now is how to get it to pull the checkin and checkout columns from the table for EACH ROW.
What difference does it make where any other data comes from?

Scott
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000