SQL Calendar table script for MS SQL Server and MySQL databases. Populates in about 2 seconds.

Calendar_MSSQL.sql

/* Create Calendar table for MS SQL Server (Total runtime is less than 2 seconds to populate) */
-- DROP TABLE IF EXISTS Calendar
CREATE TABLE Calendar (
    CalendarDate DATE NOT NULL PRIMARY KEY,
    CalendarYear SMALLINT NULL,
    CalendarMonth tinyint NULL,
    CalendarDay tinyint NULL,
    CalendarMonthName VARCHAR(9) NULL,
    CalendarDayName VARCHAR(9) NULL,
    CalendarDayofWeek tinyint NULL,
    CalendarIsWeekday bit NULL,
    CalendarQuarter tinyint NULL
);

-- BEGIN: Populate CalendarDate field using high performance method
DECLARE @Start datetime = '2020-01-01';
DECLARE @End  datetime = '2099-12-31';

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 )

INSERT INTO Calendar (CalendarDate)
SELECT  @Start+n-1 AS Date
FROM    (
            SELECT ROW_NUMBER() OVER (ORDER BY n)
            FROM Nbrs 
        ) D ( n )
WHERE   n <= DATEDIFF(day, @Start, @End) +1;

-- END: Populate CalendarDate field using high performance method

-- Now update other fields
UPDATE Calendar
SET    CalendarYear = YEAR(CalendarDate), -- 2022 (Results for 2022-03-17)
       CalendarMonth = MONTH(CalendarDate), -- 3
       CalendarDay = DAY(CalendarDate), -- 17
       CalendarMonthName = DATENAME(MONTH, CalendarDate), -- March
       CalendarDayName = DATENAME(WEEKDAY, CalendarDate), -- Thursday
       CalendarDayofWeek = DATEPART(WEEKDAY, CalendarDate), -- 5
       CalendarIsWeekday = CASE WHEN DATEPART(DW, CalendarDate) IN (1,7) THEN 0 ELSE 1 END, -- 1
       CalendarQuarter = DATEPART(QUARTER, CalendarDate); -- 1
    
-- SELECT * FROM Calendar ORDER BY 1

/*
First record from table:

CalendarDate CalendarYear CalendarMonth CalendarDay CalendarMonthName CalendarDayName CalendarDayofWeek CalendarIsWeekday CalendarQuarter
------------ ------------ ------------- ----------- ----------------- --------------- ----------------- ----------------- ---------------
2020-01-01   2020         1             1           January           Wednesday       4                 1                 1
*/

Calendar_MySQL.sql

/* Create calendar table for MySQL (Total runtime is less than 2 seconds to populate) */
-- DROP TABLE IF EXISTS calendar
CREATE TABLE calendar (
    CalendarDate DATE NOT NULL PRIMARY KEY,
    CalendarYear SMALLINT NULL,
    CalendarMonth tinyint NULL,
    CalendarDay tinyint NULL,
    CalendarMonthName VARCHAR(9) NULL,
    CalendarDayName VARCHAR(9) NULL,
    CalendarDayofWeek tinyint NULL,
    CalendarIsWeekday tinyint NULL,
    CalendarQuarter tinyint NULL
);

-- BEGIN: Populate calendardate field using high performance method
DROP TABLE IF EXISTS calendar_ints; -- Note, we can't use a temporary table because we are going to self join to it
CREATE TABLE IF NOT EXISTS calendar_ints ( i tinyint );
INSERT INTO calendar_ints VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

INSERT INTO calendar (calendardate)
SELECT DATE('2020-01-01') + INTERVAL a.i*10000 + b.i*1000 + c.i*100 + d.i*10 + e.i DAY
FROM calendar_ints a JOIN calendar_ints b JOIN calendar_ints c JOIN calendar_ints d JOIN calendar_ints e
WHERE (a.i*10000 + b.i*1000 + c.i*100 + d.i*10 + e.i) < (365.25 * 80) -- Populate till 2099-12-31 (365.25 days per year * 80 years) 
ORDER BY 1;

DROP TABLE IF EXISTS calendar_ints;
-- END: Populate calendardate field using high performance method

-- Now update other fields
UPDATE calendar
SET    CalendarYear = YEAR(CalendarDate), -- 2022 (Results for 2022-03-17)
       CalendarMonth = MONTH(CalendarDate), -- 3
       CalendarDay = dayofmonth(CalendarDate), -- 17
       CalendarMonthName = monthname(CalendarDate), -- March
       CalendarDayName = dayname(CalendarDate), -- Thursday
       CalendarDayofWeek = dayofweek(CalendarDate), -- 5
       CalendarIsWeekday = CASE WHEN dayofweek(CalendarDate) IN (1,7) THEN 0 ELSE 1 END, -- 1
       CalendarQuarter = quarter(CalendarDate); -- 1
    
-- select * from calendar order by 1

/*
First record from table:

CalendarDate CalendarYear CalendarMonth CalendarDay CalendarMonthName CalendarDayName CalendarDayofWeek CalendarIsWeekday CalendarQuarter
------------ ------------ ------------- ----------- ----------------- --------------- ----------------- ----------------- ---------------
2020-01-01   2020         1             1           January           Wednesday       4                 1                 1
*/