View Source

Please find below a calendar creation script for Microsoft SQL and Data Warehouse implementations.

One of the extremely useful features of this is the stored calculation of various data attributes (eg. Quarter, Day of Week, Holidays, Work Days). This eliminates the need for complex SQL, transformations in ETL or aggregation code resulting in greatly improved performance for databases and business intelligence applications.

The script will step through the following:

Create a schema named "reference"
Create a function for the calculation of Easter holidays (it's complicated how they come up with it, so this makes it easier)
Create four tables named "calendar", "public_holidays", "timezone" and "timezone_adjustment_rule"
Populate the calendar with base calendar data, the default is 1st Jan 1900 to 31st Dec 2099 and this can be changed in the declaration portion of the script
Process and update the calendar attributes
Populate the public holiday table (I have it defaulted to QLD, Australia, but you can modify this section how you like)
Update the calendar table with the public holiday dates and names (defaulted to QLD, Australia, but you can change how you want the holidays processed in the declaration portion of the script)
Process and update the timezone and daylight savings attributes from populated timezone information

If you wish you can add custom public holiday dates to the public_holiday table, the script, when it runs again will only purge the public holiday entries that have "system" as the source.

To run the script, open the SQL file in SQL Server Management Studio, select the database you want to run the script against, verify your declared variables and execute. The script has also been configured to run multiple times, if needed.

Download: MSSQL_Calendar_v0.4.4.zip

Schema:

Column Name	Data Type
Calendar_Date	date
Calendar_Year	int
Is_Leap_Year	int
Calendar_Quarter	int
Calendar_Quarter_Name	varchar
Calendar_Month	int
Month_Name	varchar
Month_Year_Name	varchar
Short_Month_Name	varchar
Short_Month_Year_Name	varchar
Calendar_Day	int
Calendar_Day_Suffix	varchar
Calendar_Day_Name	varchar
Is_Work_Day	int
Work_Day_Number_in_year	int
Work_Day_Number_in_month	int
Work_Day_Number_in_week	int
Year_Day_Name_Instance	int
Quarter_Day_Name_Instance	int
Month_Day_Name_Instance	int
Year_Month	varchar
Weeks_in_Year	int
Week_of_Year	int
Week_of_Quarter	int
Weeks_in_Month	int
Week_of_Month	int
Day_of_Week	int
Is_WeekDay	int
Is_First_Weekday_in_Month	int
First_Day_in_Month	date
Last_Day_in_Month	date
Is_Last_Day_in_Month	int
Is_Last_Weekday_in_Month	int
Year_Week	varchar
First_Day_in_Week	date
Last_Day_in_Week	date
Is_Week_in_Same_Month	int
Days_in_Year	int
Days_Completed_in_Year	int
Days_Remaining_in_Year	int
Year_Quarter	varchar
Days_in_Quarter	int
Days_Completed_in_Quarter	int
Days_Remaining_in_Quarter	int
WeekDays_in_Quarter	int
Weekdays_Completed_in_Quarter	int
Weekdays_Remaining_in_Quarter	int
Days_in_Month	int
Month_Days_Remaining	int
WeekDays_in_Month	int
Month_Weekdays_Completed	int
Month_Weekdays_Remaining	int
First_Day_in_Quarter	date
Last_Day_in_Quarter	date
Is_Last_Day_in_Quarter	int
Is_Holiday	int
Holiday_Name	varchar
FY_YearName	varchar
FY_StartYear	int
FY_StartDate	date
FY_Quarter	int
FY_Quarter_Name	varchar
FY_Month	int
FY_EndYear	int
FY_EndDate	date
Is_DST	int
DST_StartTime	time
DST_EndTime	time
UTC_Offset_Seconds	int
UTC_Offset_DST_Seconds	int

Column Name	Data Type
Calendar_Date	date
Holiday_Name	varchar
Information	varchar
Country	varchar
Jurisdiction	varchar
Source	varchar

Column Name	Data Type
Id	int
Identifier	nvarchar
StandardName	nvarchar
DisplayName	nvarchar
DaylightName	nvarchar
SupportsDaylightSavingTime	bit
BaseUtcOffsetSec	int

Column Name	Data Type
Id	int
TimezoneId	int
RuleNo	int
DateStart	datetime2
DateEnd	datetime2
DaylightTransitionStartIsFixedDateRule	bit
DaylightTransitionStartMonth	int
DaylightTransitionStartDay	int
DaylightTransitionStartWeek	int
DaylightTransitionStartDayOfWeek	int
DaylightTransitionStartTimeOfDay	time
DaylightTransitionEndIsFixedDateRule	bit
DaylightTransitionEndMonth	int
DaylightTransitionEndDay	int
DaylightTransitionEndWeek	int
DaylightTransitionEndDayOfWeek	int
DaylightTransitionEndTimeOfDay	time
DaylightDeltaSec	int

Change Log:

v0.4.4
Short Month Format Fix

Modified:

Code Fix to update Short Month Fields

v0.4.3
Queensland Public Holiday Update for 2020 Onwards, Date Format Additions

Added:

Use Database Declaration
Various Date Format Strings and useful variations

Modified:

Queensland Public Holidays during COVID-19,
Queen's Birthday End 2022 and King's Birthday Start 2023+
Collation Error Fix

v0.4.2
Added Default UTC and DST UTC Columns to the table and view

Added:

Added Default UTC and DST UTC Columns to the table and view

Modified:

Changed UTC values from hours to seconds

v0.4.1
Added a Data Analytics View to convert column names into human readable format

Added:

A view on the data calendar table to convert column names into human readable format

v0.4.0
Added Timezone and Daylight Savings attributes

Added:

Timezone and Daylight Savings attributes
Is First Weekday of Month
Is Last Weekday of Month

v0.3.2
Initial run fix. Formatting enhancements. Schema enhancements

Modified:

Reordered the schema detect and creation script to run without error.
Various formatting enhancements to the layout of the script
Updated the alter table not null modification scripts to include the new columns from work days and financial year modifications

Removed:

Non-Clustered indexing

v0.3.1
Updated so that public holidays are not counted as work days.

Modified:

Public Holidays are not counted as work days.

v0.3.0
Work Day attributes. Reordered columns for easier readability. Script formatting enhancements

Added:

Added Work Day columns and attributes

Modified:

Reordered columns for easier readability
Script formatting enhancements

v0.2.0
Added Financial Year attribute data

Added:

Added Financial Year columns and attributes

v0.1.1
Is_Holiday not null constraint, other minor edits

Added:

Included the Is_Holiday not null constraint after the public holiday update section

Modified:

Other minor general script formatting changes

v0.1.0
First release of script

Added:

Drop and create the Easter Holiday function
Drop and create the calendar table
Create the public holidays table if required
Populate base date values in the calendar table'
Calendar Table Update 1: Populate Additional Date Xref Table Fields (Pass 1)
Calendar Table Update 2: Populate Additional Date Xref Table Fields (Pass 2)
Calendar Table Update 3: Populate Additional Date Xref Table Fields (Pass 3)
Calendar Table Update 4: Populate Additional Date Xref Table Fields (Pass 4)
Calendar Table Update 5: Populate Additional Date Xref Table Fields (Pass 5)
Calendar Table Update 6: Populate Additional Date Xref Table Fields (Pass 6)
Populate Public Holiday Table - Internationally Recognised Public Holidays
Populate Public Holiday Table - Australian Holidays
Populate Public Holiday Table - Queensland Holidays
Update main calendar table with public holiday flags