p.campbell p.campbell - 7 months ago 12
SQL Question

Generate a resultset of incrementing dates in TSQL

Consider the need to create a resultset of dates. We've got start and end dates, and we'd like to generate a list of dates in between.

DECLARE @Start datetime
,@End datetime
DECLARE @AllDates table
(@Date datetime)

SELECT @Start = 'Mar 1 2009', @End = 'Aug 1 2009'

--need to fill @AllDates. Trying to avoid looping.
-- Surely if a better solution exists.


Consider the current implementation with a
WHILE
loop:

DECLARE @dCounter datetime
SELECT @dCounter = @Start
WHILE @dCounter <= @End
BEGIN
INSERT INTO @AllDates VALUES (@dCounter)
SELECT @dCounter=@dCounter+1
END


Question: How would you create a set of dates that are within a user-defined range using T-SQL? Assume SQL 2005+. If your answer is using SQL 2008 features, please mark as such.

Answer

If your dates are no more than 2047 days apart:

declare @dt datetime, @dtEnd datetime
set @dt = getdate()
set @dtEnd = dateadd(day, 100, @dt)

select dateadd(day, number, @dt)
from 
    (select distinct number from master.dbo.spt_values
     where name is null
    ) n
where dateadd(day, number, @dt) < @dtEnd