Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Dateadd Expand / Collapse
Author
Message
Posted Friday, March 9, 2012 7:04 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, July 30, 2013 9:15 AM
Points: 87, 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


Post #1264357
Posted Friday, March 9, 2012 7:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:51 AM
Points: 5,438, Visits: 10,140
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
Post #1264371
Posted Friday, March 9, 2012 7:26 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, July 30, 2013 9:15 AM
Points: 87, 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
Post #1264390
Posted Friday, March 9, 2012 7:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:51 AM
Points: 5,438, Visits: 10,140
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
Post #1264397
Posted Friday, March 9, 2012 9:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
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
Post #1264498
Posted Friday, March 9, 2012 11:30 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, July 30, 2013 9:15 AM
Points: 87, Visits: 316
Thanks a lot you guys. Not only did I get my result, I learned where I was making the mistake!
Post #1264543
Posted Friday, March 9, 2012 11:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
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
Post #1264550
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse