Generate a Series of Numbers and Dates in SQL Server

sqlSQLsql-serverdatetimecte

A recursive CTE that produces a numbered series of consecutive dates. Set the first and last number, change the start date, and raise MAXRECURSION for longer series. Works on every SQL Server version (the built-in GENERATE_SERIES only exists from SQL Server 2022).

DECLARE @startnum INT = 1;
DECLARE @endnum INT = 10;

WITH gen AS (
    SELECT DATEADD(DAY, @startnum, '2020-12-30') AS DT, @startnum AS num
    UNION ALL
    SELECT DATEADD(DAY, num + 1, '2020-12-30') AS DT, num + 1
    FROM gen
    WHERE num + 1 <= @endnum
)
SELECT num, DT
FROM gen
ORDER BY DT DESC
OPTION (MAXRECURSION 10000);

-- Result: 10 rows, from  num 10 = 2021-01-09  down to  num 1 = 2020-12-31

Comments

Loading comments…