Created
March 17, 2022 22:04
-
-
Save randyburden/7e86e8c73c97a5482cdf6d803c0312db to your computer and use it in GitHub Desktop.
SQL Calendar table script for MS SQL Server and MySQL databases. Populates in about 2 seconds.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* 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 | |
*/ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* 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 | |
*/ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment