Create a persistent DimDate (calendar) table for a data warehouse using generate_series function

The GENERATE_SERIES function is a table-valued function that returns a single-column table named value. SQL Server introduced the built-in GENERATE_SERIES function in SQL Server 2022 to create a sequence of numbers. For older versions of SQL Server, we must use alternative methods like recursive CTEs. 

Before 2022 to generate the date series we are using recursive CTE. Limitation of Recursive function is limit of MAXRECURSION 32767. If it is more than it, we got an error. If we want to generate the 100 years table.

Let’s create a DimDate table.

CREATE TABLE dbo.DimDate (     DateKey        INT         NOT NULL PRIMARY KEY,  -- 20250314     FullDate       DATE        NOT NULL,     DayNumber      TINYINT     NOT NULL,     DayName        VARCHAR(10) NOT NULL,               WeekNumber     TINYINT     NOT NULL,     ISOWeekNumber  TINYINT     NOT NULL,     MonthNumber    TINYINT     NOT NULL,     MonthName      VARCHAR(10) NOT NULL,     QuarterNumber  TINYINT     NOT NULL,     YearNumber     SMALLINT    NOT NULL,     IsWeekend      BIT         NOT NULL,     IsHoliday      BIT         NOT NULL,     IsWorkingDay   BIT         NOT NULL,    Create_dt      DATETIME    NOT NULL  CONSTRAINT   DimDate_create_dt default getdate(), Create_user    varchar(30) NOT NULL  CONSTRAINT DimDate_create_user default system_user,   Update_dt      DATETIME    NOT NULL  CONSTRAINT DimDate_Update_dt default getdate(), Update_user    varchar(30) NOT NULL  CONSTRAINT DimDate_update_user default system_user );

Table created successfully.

Below is script to generate the table.

WITH gs      AS (SELECT Dateadd(day, value, '2000-01-01') AS Value          FROM   Generate_series(0, 18627)) INSERT INTO dbo.dimdate             (datekey,              fulldate,              daynumber,              dayname,              weeknumber,              isoweeknumber,              monthnumber,              monthname,              quarternumber,              yearnumber,              isweekend,              isholiday,              isworkingday) SELECT CONVERT(INT, Format(gs.value, 'yyyyMMdd')) AS DateKey,        gs.value                                   AS FullDate,        Day(gs.value)                              AS DayNumber,        Datename(weekday, gs.value)                AS DayName,        Datepart(week, gs.value)                   AS WeekNumber,        Datepart(iso_week, gs.value)               AS ISOWeekNumber,        Month(gs.value)                            AS MonthNumber,        Datename(month, gs.value)                  AS MonthName,        Datepart(quarter, gs.value)                AS QuarterNumber,        Year(gs.value)                             AS YearNumber,        CASE          WHEN Datename(weekday, gs.value) IN ( 'Saturday', 'Sunday' ) THEN 1          ELSE 0        END                                        AS IsWeekend,        0                                          AS IsHoliday,-- default        CASE          WHEN Datename(weekday, gs.value) IN ( 'Saturday', 'Sunday' ) THEN 0          ELSE 1        END                                        AS IsWorkingDay FROM   gs;

See the table.

DimDate is populated successfully.

For the holiday currently we have marked as 0 for the all day. We need to update every year holiday list. Either we can update or we can maintain a holiday table and using this table we will update the DimDate table.

Creating holiday Master table

CREATE TABLE dbo.HolidayMaster (     HolidayDate DATE PRIMARY KEY,     HolidayName VARCHAR(50) );

Here we are inserting holiday list.

INSERT INTO dbo.HolidayMaster (HolidayDate,HolidayName) VALUES ('2026-01-26','Republic Day'), ('2026-08-15','Independence Day'), ('2026-10-02','Gandhi Jayanti'), ('2026-12-25','Christmas');

Let’s update DimDate table.

UPDATE d SET    d.IsHoliday = 1,    d.IsWorkingDay = 0 FROM dbo.DimDate d JOIN dbo.HolidayMaster h    ON d.FullDate = h.HolidayDate;

Table updated.

Let’s see the DimDate table

See for Monday, Friday is also showing isworkingDay as 0 because these day marked as Holidays.