SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dateadd


Dateadd

Author
Message
bostonjoey
bostonjoey
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 Visits: 316
Hello,

I am trying to accomplish a simple task. I want to display the dates (DATE) format in succesive rows, between the parameters I defined. I am missing the logic here...I don't get it. I am new to SQL! HELP!

I think its because I dont understand how the while loop works. What I think I should do is have the variable @i increase by one to a DATEAdd function (increasing the date from the previous row) until its equal to the counter

Here is my code. Any suggestions will help!

DECLARE @currentdate DATETIME
DECLARE @followingdate DATETIME
Declare @daycounter Int
declare @i int

SET @currentdate = GETDATE()
Set @daycounter = '30'
set @i = '1'

while @i <= @daycounter
Begin
SET @followingdate = DATEADD(DD, @i, @currentdate)
set @i +1
End

SELECT @currentdate, @followingdate
John Mitchell-245523
John Mitchell-245523
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18510 Visits: 16217
All that's going to do is set @followingdate to tomorrow's date 30 times, but not display anything until the end. Please will you show what results you expect to see, and if you happen to be selecting any data from tables, provide table DDL and sample data.

Thanks
John
bostonjoey
bostonjoey
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 Visits: 316
Thanks for the quick reply. Basically, I have a Date Dimension for the next 2 years, i want to load each date for the next 5 years. I need to generate the rows for the next 5 years.

Here is what I want to see, but with different years.

Date
1997-01-01
1997-01-02
1997-01-03
1997-01-04
1997-01-05
1997-01-06
1997-01-07
1997-01-08
John Mitchell-245523
John Mitchell-245523
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18510 Visits: 16217
Search for tally (numbers) tables on this site. You can either create a permanent one, or you can do it on the fly with a CTE. Once you have one, you can get the results you're looking for easily and without resorting to a loop. Suppose your CTE or table is called Numbers, and the number column is called N. You'd do something like this:

SELECT DATEADD(dd,N-1,'19970101')
FROM Numbers
WHERE N <= <maximum_no_of_dates_needed>

John
bitbucket-25253
bitbucket-25253
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9445 Visits: 25280
Here is an idea for you

DECLARE @Date DATETIME
SET @Date = GETDATE()
WHILE @Date < '2013-01-01'
BEGIN
INSERT INTO CalDay
( DayDate, NextDay, DayNum, NameOfDay )
SELECT dateadd(day,datediff(day, 0,@Date),0)
, dateadd(day,datediff(day, 1,@Date),0)
, DATEPART(WEEKDAY, @Date)
, CASE DATEPART(weekday, @Date)
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
ELSE 'Saturday' END;
SET @Date = DATEADD(day, 1, @Date);
END
GO

SELECT * FROM CALDAY
Sample results:
2012-03-09 00:00:00.000 2012-03-08 00:00:00.000 6 Friday
2012-03-10 00:00:00.000 2012-03-09 00:00:00.000 7 Saturday
2012-03-11 00:00:00.000 2012-03-10 00:00:00.000 1 Sunday
2012-03-12 00:00:00.000 2012-03-11 00:00:00.000 2 Monday


Just modify the WHILE @Date < '2013-01-01'
to the year you need / desire to stop adding to the table.

Whoops NextDay should be titled priorday or previousday.

And of course edit out the fields that you do not need and adjust the T-SQL accordingly.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
bostonjoey
bostonjoey
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 Visits: 316
Thanks a lot you guys. Not only did I get my result, I learned where I was making the mistake!
bitbucket-25253
bitbucket-25253
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9445 Visits: 25280
And thank you ... it is nice for everyone who assists others to know that they did some good ... either directly or indirectly

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search