Generating a calendar

Produce thousands of rows without writing a single one: the numbers table.

≈ 2 min readReading

On this page

Problem statement
Data set
Procedural versus set-based
1. Procedural
2. Set-based
Expected result
Why set-based is preferable
Pitfalls
Variants
Verifying equivalence
SQL Server versions

Problem statement#

Fill dbo.Calendrier with one day per row, from January 1, 2020 to December 31, 2029, with the year, the month and the day of the week (ISO standard).

Data set#

CREATE TABLE dbo.Calendrier
(
    Jour    date     NOT NULL PRIMARY KEY,
    Annee   smallint NOT NULL,
    Mois    tinyint  NOT NULL,
    JourISO tinyint  NOT NULL      -- 1 = Monday … 7 = Sunday
);

Procedural versus set-based#

Procedural#

DECLARE @Jour date = '2020-01-01';

WHILE @Jour <= '2029-12-31'
BEGIN
    INSERT INTO dbo.Calendrier (Jour, Annee, Mois, JourISO)
    VALUES (@Jour,
            YEAR(@Jour),
            MONTH(@Jour),
            (DATEDIFF(DAY, CAST('19000101' AS date), @Jour) % 7) + 1);

    SET @Jour = DATEADD(DAY, 1, @Jour);
END;

Set-based#

WITH N AS
(
    SELECT TOP (3653)
           CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS int) AS n
    FROM   sys.all_columns AS a
    CROSS JOIN sys.all_columns AS b
)
INSERT INTO dbo.Calendrier (Jour, Annee, Mois, JourISO)
SELECT d.Jour,
       YEAR(d.Jour),
       MONTH(d.Jour),
       (DATEDIFF(DAY, CAST('19000101' AS date), d.Jour) % 7) + 1
FROM   N
CROSS APPLY (SELECT DATEADD(DAY, N.n, CAST('2020-01-01' AS date)) AS Jour) AS d;

Expected result#

3,653 rows, from January 1, 2020 to December 31, 2029 (ten years, including three leap years: 2020, 2024, 2028).

Why set-based is preferable#

A numbers table obtained by Cartesian product supplies the 3,653 integers without a loop; CROSS APPLY turns each one into a date.
The procedural version runs 3,653 inserts, each committed separately if no transaction is open.
A calendar is typically a table created once: the absolute gain is small here, but the pattern is useful for generating large series (test data sets, time slots, grids).

Pitfalls#

Day of the week: DATEPART(weekday, …) depends on SET DATEFIRST and DATENAME on SET LANGUAGE. The calculation using DATEDIFF from January 1, 1900 (a Monday) is independent of session settings: (DATEDIFF(DAY, '19000101', Jour) % 7) + 1 gives 1 for Monday.
Size of the numbers table: the Cartesian product of sys.all_columns with itself easily supplies 3,653 rows; for larger series, add a third CROSS JOIN.
Recursion: generating dates with a recursive CTE works, but level by level and limited by MAXRECURSION (default value 100); it is not the best option for large series.

Variants#

-- SQL Server 2022 and Azure SQL: GENERATE_SERIES
INSERT INTO dbo.Calendrier (Jour, Annee, Mois, JourISO)
SELECT d.Jour, YEAR(d.Jour), MONTH(d.Jour), (DATEDIFF(DAY, CAST('19000101' AS date), d.Jour) % 7) + 1
FROM   GENERATE_SERIES(0, 3652) AS g
CROSS APPLY (SELECT DATEADD(DAY, g.value, CAST('2020-01-01' AS date)) AS Jour) AS d;

Verifying equivalence#

A change of method is only worthwhile if the result is identical. Compare in both directions; no rows should come out.

SELECT COUNT(*) AS NbJours, MIN(Jour) AS Premier, MAX(Jour) AS Dernier FROM dbo.Calendrier;
-- Expected: 3653, 2020-01-01, 2029-12-31

-- No gaps: each day is exactly 1 day after the previous one
SELECT Jour FROM (SELECT Jour, LAG(Jour) OVER (ORDER BY Jour) AS Prec FROM dbo.Calendrier) AS x
WHERE Prec IS NOT NULL AND DATEDIFF(DAY, Prec, Jour) <> 1;
-- Expected: no rows.

SQL Server versions#

Numbers table by Cartesian product: all versions. GENERATE_SERIES: SQL Server 2022 and Azure SQL.