Generate a resultset of incrementing dates in TSQL

If your dates are no more than 2047 days apart:

declare @dt datetime, @dtEnd datetimeset @dt = getdate()set @dtEnd = dateadd(day, 100, @dt)select dateadd(day, number, @dt)from     (select number from master.dbo.spt_values     where [type] = 'P'    ) nwhere 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.

Tthe following uses a recursive CTE (SQL Server 2005+):

WITH dates AS (     SELECT CAST('2009-01-01' AS DATETIME) 'date'     UNION ALL     SELECT DATEADD(dd, 1, t.date)        FROM dates t      WHERE DATEADD(dd, 1, t.date) <= '2009-02-01')SELECT ...  FROM TABLE t  JOIN dates d ON d.date = t.date --etc.

@KM's answer creates a numbers table first, and uses it to select a range of dates. To do the same without the temporary numbers table:

DECLARE  @Start datetime         ,@End  datetimeDECLARE @AllDates table        (Date datetime)SELECT @Start = 'Mar 1 2009', @End = 'Aug 1 2009';WITH Nbrs_3( n ) AS ( SELECT 1 UNION SELECT 0 ),     Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ),     Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),     Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),     Nbrs  ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )    SELECT @Start+n-1 as Date        FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n)            FROM Nbrs ) D ( n )    WHERE n <= DATEDIFF(day,@Start,@End)+1 ;

Test of course, if you are doing this often, a permanent table may well be more performant.

The query above is a modified version from this article, which discusses generating sequences and gives many possible methods. I liked this one as it does not create a temp table, and is not limited to the number of elements in the sys.objects table.