Generate a resultset of incrementing dates in TSQL

Question

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 1

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 number from master.dbo.spt_values      where [type] = 'P'     ) n where dateadd(day, number, @dt) < @dtEnd 

I updated my answer after several requests to do so. Why?

The original answer contained the subquery

 select distinct number from master.dbo.spt_values      where name is null 

which delivers the same result, as I tested them on SQL Server 2008, 2012, and 2016.

However, as I tried to analyze the code that MSSQL internally when querying from spt_values, I found that the SELECT statements always contain the clause WHERE [type]='[magic code]'.

Therefore I decided that although the query returns the correct result, it delivers the correct result for wrong reasons:

There may be a future version of SQL Server which defines a different [type] value which also has NULL as values for [name], outside the range of 0-2047, or even non-contiguous, in which case the result would be simply wrong.