Dynamically Generated List of Dates Using Common Table Expressions (CTE)

Here is a nice use for Common Table Expressions (CTEs) to generate a dates table or other list of stuff. I won't take credit for this technique since I saw it someplace else (but can't remember where). Of course my blog is for stuff I want to remember, so thanks to whoever you are...

declare @loops int = 1000
declare @StartDate date = '1/1/2000';

WITH CountTable( RowNumber, [Month], [Year], FirstDay ) AS
(
 SELECT
  1 RowNumber,
  MONTH(@StartDate) [Month],
  YEAR(@StartDate) [Year],
  -- compute the first day of the month from whatever day was provided
  DATEADD(dd,-(DAY(@StartDate)-1),@StartDate) FirstDay   
 UNION ALL
 
 SELECT
  RowNumber + 1 RowNumber,
  MONTH(DATEADD(MONTH, 1, FirstDay)),
  YEAR(DATEADD(MONTH, 1, FirstDay)),
  DATEADD(MONTH, 1, FirstDay) FirstDay
  
 FROM CountTable t
 WHERE RowNumber < @loops
)

SELECT * 
FROM CountTable
OPTION (maxrecursion 32767)

The results look like this:

 1  1 2000 2000-01-01
 2  2 2000 2000-02-01
 3  3 2000 2000-03-01
 4  4 2000 2000-04-01
 5  5 2000 2000-05-01
 6  6 2000 2000-06-01
 7  7 2000 2000-07-01
 8  8 2000 2000-08-01
 9  9 2000 2000-09-01
10 10 2000 2000-10-01
11 11 2000 2000-11-01
12 12 2000 2000-12-01
13  1 2001 2001-01-01
14  2 2001 2001-02-01

more rows here...

 999 3 2083 2083-03-01
1000 4 2083 2083-04-01

In this query, there are two parts to the CTE, the seed (top part) and the function (bottom part). The seed sets the initial values, and the function uses the seed values to create the next row of values, which are then used to create the following row of values (and so on, and so on...)

There are a couple of things to notice:

• The function values must be based on the previous value (notice my recalculation of the next month)
• You must use a semicolon before the CTE
• the maxrecursion option defaults at 100 iterations and has a maximum value of 32767