sql – Generate a resultset of incrementing dates in TSQL

sql – Generate a resultset of incrementing dates in TSQL

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 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.

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

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

sql – Generate a resultset of incrementing dates in TSQL

@KMs 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  datetime
DECLARE @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 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.

Leave a Reply

Your email address will not be published. Required fields are marked *