p.campbell p.campbell - 2 years ago 103
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

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

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 Source

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)
    (select distinct number from master.dbo.spt_values
     where name is null
    ) n
where dateadd(day, number, @dt) < @dtEnd
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download