March 9, 2012 at 7:04 am
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
March 9, 2012 at 7:16 am
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
March 9, 2012 at 7:26 am
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
March 9, 2012 at 7:37 am
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
March 9, 2012 at 9:57 am
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.0002012-03-08 00:00:00.0006Friday
2012-03-10 00:00:00.0002012-03-09 00:00:00.0007Saturday
2012-03-11 00:00:00.0002012-03-10 00:00:00.0001Sunday
2012-03-12 00:00:00.0002012-03-11 00:00:00.0002Monday
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.
March 9, 2012 at 11:30 am
Thanks a lot you guys. Not only did I get my result, I learned where I was making the mistake!
March 9, 2012 at 11:39 am
And thank you ... it is nice for everyone who assists others to know that they did some good ... either directly or indirectly
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply