Date Calendar

Sean Smith, 2019-04-15 (first published: 2009-10-06)

Over the years I have had some of the strangest / most complex requirements for data querying / reporting that you could imagine. The most difficult often involves some type of strange business rule or requirement surrounding a date parameter, logic, range, etc. I've put this code together in order to pre-calculate many elements of a date's attributes and be able to quickly determine various date characteristics / values without the need to break my head every time things get hairy.

To run it, simply choose the database in which you want the table to reside, assign the @Date_Start and @Date_End variables with the date range you want populated in the calendar, and execute.

Below is a listing of the output fields and their description using a date of 10/06/2009 (MM/DD/YYYY) as the reference example (the code should compensate for how any SQL Server instance is set up to handle the internal settings for start / end of week, weekdays, etc.). All values after the calendar_date field are specific to the date value found in each individual record.

calendar_date: calendar date value (2009-10-06 00:00:00.000)
calendar_year: year portion of the date (2009)
calendar_month: month portion of the date (10)
calendar_day: day portion of the date (6)
calendar_quarter: quarter in which the date value falls under (4)
first_day_in_week: first day of the week in which the date value is found (2009-10-04 00:00:00.000)
last_day_in_week: last day of the week in which the date value is found (2009-10-10 00:00:00.000)
is_week_in_same_month: is the first_day_in_week and last_day_in_week value contained within the same month (1)
first_day_in_month: first day of the month (2009-10-01 00:00:00.000)
last_day_in_month: last day of the month (2009-10-31 00:00:00.000)
is_last_day_in_month: is the date value the last day of the month (0)
first_day_in_quarter: first day of the quarter (2009-10-01 00:00:00.000)
last_day_in_quarter: last day of the quarter (2009-12-31 00:00:00.000)
is_last_day_in_quarter: is the date value the last day of the quarter (0)
day_of_week: day of the week (3)
week_of_month: week of the month (2)
week_of_quarter: week of the quarter (2)
week_of_year: week of the year (41)
days_in_month: total days in the month (31)
month_days_remaining: number of days remaining in the month (25)
weekdays_in_month: number of weekdays in the the month (22)
month_weekdays_remaining: number of weekdays remaining in the month (18)
month_weekdays_completed: number of weekdays completed in the month (4)
days_in_quarter: total days in the quarter (92)
quarter_days_remaining: number of days remaining in the quarter (86)
quarter_days_completed: number of days completed in the quarter (6)
weekdays_in_quarter: number of weekdays in the the quarter (66)
quarter_weekdays_remaining: number of weekdays remaining in the quarter (62)
quarter_days_completed: number of days completed in the quarter (6)
day_of_year: number of days completed in the year (279)
year_days_remaining: number of days remaining in the year (86)
is_weekday: is the date a weekday (1)
is_leap_year: is the date contained within a leap year (0)
day_name: full name of the day (Tuesday)
month_day_name_instance: number of occurrences of the day_name within the month up until and including the specified date (1)
quarter_day_name_instance: number of occurrences of the day_name within the quarter up until and including the specified date (1)
year_day_name_instance: number of occurrences of the day_name within the year up until and including the specified date (40)
month_name: full name of the month (October)
year_week: calendar_year and week_of_year (left padded with zeros) values concatenated (200941)
year_month: calendar_year and calendar_month (left padded with zeros) values concatenated (200910)
year_quarter: calendar_year and calendar_quarter (prefixed with a "Q") values concatenated (2009Q4)

Any friendly feedback is always welcome. Enjoy!

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SET NOCOUNT ON SET ANSI_WARNINGS OFF SET ARITHABORT OFF SET ARITHIGNORE ON SET TEXTSIZE 2147483647 ----------------------------------------------------------------------------------------------------------------------------- -- Script Details: Listing Of Standard Details Related To The Script ----------------------------------------------------------------------------------------------------------------------------- -- Purpose: Date Calendar Cross-Reference Table -- Create Date (MM/DD/YYYY): 10/29/2009 -- Developer: Sean Smith (s.smith.sql AT gmail DOT com) -- Latest Release: http://qa.sqlservercentral.com/scripts/Date/68389/ -- Script Library: http://qa.sqlservercentral.com/Authors/Scripts/Sean_Smith/776614/ -- LinkedIn Profile: https://www.linkedin.com/in/seanmsmith/ ----------------------------------------------------------------------------------------------------------------------------- -- Modification History: Listing Of All Modifications Since Original Implementation ----------------------------------------------------------------------------------------------------------------------------- -- Description: Fixed Bug Affecting "month_weekdays_remaining" And "quarter_weekdays_remaining" Columns -- Date (MM/DD/YYYY): 07/02/2014 ----------------------------------------------------------------------------------------------------------------------------- -- Declarations / Sets: Declare And Set Variables ----------------------------------------------------------------------------------------------------------------------------- DECLARE @Date_Start AS DATETIME ,@Date_End AS DATETIME SET @Date_Start = '20000101' SET @Date_End = '20501231' ----------------------------------------------------------------------------------------------------------------------------- -- Error Trapping: Check If Permanent Table(s) Already Exist(s) And Drop If Applicable ----------------------------------------------------------------------------------------------------------------------------- IF OBJECT_ID (N'dbo.date_calendar', N'U') IS NOT NULL BEGIN DROP TABLE dbo.date_calendar END ----------------------------------------------------------------------------------------------------------------------------- -- Permanent Table: Create Date Xref Table ----------------------------------------------------------------------------------------------------------------------------- CREATE TABLE dbo.date_calendar ( calendar_date DATETIME NOT NULL CONSTRAINT PK_date_calendar_calendar_date PRIMARY KEY CLUSTERED ,calendar_year SMALLINT NULL ,calendar_month TINYINT NULL ,calendar_day TINYINT NULL ,calendar_quarter TINYINT NULL ,first_day_in_week DATETIME NULL ,last_day_in_week DATETIME NULL ,is_week_in_same_month INT NULL ,first_day_in_month DATETIME NULL ,last_day_in_month DATETIME NULL ,is_last_day_in_month INT NULL ,first_day_in_quarter DATETIME NULL ,last_day_in_quarter DATETIME NULL ,is_last_day_in_quarter INT NULL ,day_of_week TINYINT NULL ,week_of_month TINYINT NULL ,week_of_quarter TINYINT NULL ,week_of_year TINYINT NULL ,days_in_month TINYINT NULL ,month_days_remaining TINYINT NULL ,weekdays_in_month TINYINT NULL ,month_weekdays_remaining TINYINT NULL ,month_weekdays_completed TINYINT NULL ,days_in_quarter TINYINT NULL ,quarter_days_remaining TINYINT NULL ,quarter_days_completed TINYINT NULL ,weekdays_in_quarter TINYINT NULL ,quarter_weekdays_remaining TINYINT NULL ,quarter_weekdays_completed TINYINT NULL ,day_of_year SMALLINT NULL ,year_days_remaining SMALLINT NULL ,is_weekday INT NULL ,is_leap_year INT NULL ,day_name VARCHAR (10) NULL ,month_day_name_instance TINYINT NULL ,quarter_day_name_instance TINYINT NULL ,year_day_name_instance TINYINT NULL ,month_name VARCHAR (10) NULL ,year_week CHAR (6) NULL ,year_month CHAR (6) NULL ,year_quarter CHAR (6) NULL ) ----------------------------------------------------------------------------------------------------------------------------- -- Table Insert: Populate Base Date Values Into Permanent Table Using Common Table Expression (CTE) ----------------------------------------------------------------------------------------------------------------------------- ;WITH CTE_Date_Base_Table AS ( SELECT @Date_Start AS calendar_date UNION ALL SELECT DATEADD (DAY, 1, cDBT.calendar_date) FROM CTE_Date_Base_Table cDBT WHERE DATEADD (DAY, 1, cDBT.calendar_date) <= @Date_End ) INSERT INTO dbo.date_calendar ( calendar_date ) SELECT cDBT.calendar_date FROM CTE_Date_Base_Table cDBT OPTION (MAXRECURSION 0) ----------------------------------------------------------------------------------------------------------------------------- -- Table Update I: Populate Additional Date Xref Table Fields (Pass I) ----------------------------------------------------------------------------------------------------------------------------- UPDATE dbo.date_calendar SET calendar_year = DATEPART (YEAR, calendar_date) ,calendar_month = DATEPART (MONTH, calendar_date) ,calendar_day = DATEPART (DAY, calendar_date) ,calendar_quarter = DATEPART (QUARTER, calendar_date) ,first_day_in_week = DATEADD (DAY, -DATEPART (WEEKDAY, calendar_date) + 1, calendar_date) ,first_day_in_month = CONVERT (VARCHAR (6), calendar_date, 112) + '01' ,day_of_week = DATEPART (WEEKDAY, calendar_date) ,week_of_year = DATEPART (WEEK, calendar_date) ,day_of_year = DATEPART (DAYOFYEAR, calendar_date) ,is_weekday = (CASE WHEN ((@@DATEFIRST - 1) + (DATEPART (WEEKDAY, calendar_date) - 1)) % 7 NOT IN (5, 6) THEN 1 ELSE 0 END) ,day_name = DATENAME (WEEKDAY, calendar_date) ,month_name = DATENAME (MONTH, calendar_date) ALTER TABLE dbo.date_calendar ALTER COLUMN calendar_year INT NOT NULL ALTER TABLE dbo.date_calendar ALTER COLUMN calendar_month INT NOT NULL ALTER TABLE dbo.date_calendar ALTER COLUMN calendar_day INT NOT NULL ALTER TABLE dbo.date_calendar ALTER COLUMN calendar_quarter INT NOT NULL ALTER TABLE dbo.date_calendar ALTER COLUMN first_day_in_week DATETIME NOT NULL ALTER TABLE dbo.date_calendar ALTER COLUMN first_day_in_month DATETIME NOT NULL ALTER TABLE dbo.date_calendar ALTER COLUMN day_of_week INT NOT NULL ALTER TABLE dbo.date_calendar ALTER COLUMN week_of_year INT NOT NULL ALTER TABLE dbo.date_calendar ALTER COLUMN day_of_year INT NOT NULL ALTER TABLE dbo.date_calendar ALTER COLUMN is_weekday INT NOT NULL ALTER TABLE dbo.date_calendar ALTER COLUMN day_name VARCHAR (10) NOT NULL ALTER TABLE dbo.date_calendar ALTER COLUMN month_name VARCHAR (10) NOT NULL CREATE NONCLUSTERED INDEX IX_date_calendar_calendar_year ON dbo.date_calendar (calendar_year) CREATE NONCLUSTERED INDEX IX_date_calendar_calendar_month ON dbo.date_calendar (calendar_month) CREATE NONCLUSTERED INDEX IX_date_calendar_calendar_quarter ON dbo.date_calendar (calendar_quarter) CREATE NONCLUSTERED INDEX IX_date_calendar_first_day_in_week ON dbo.date_calendar (first_day_in_week) CREATE NONCLUSTERED INDEX IX_date_calendar_day_of_week ON dbo.date_calendar (day_of_week) CREATE NONCLUSTERED INDEX IX_date_calendar_is_weekday ON dbo.date_calendar (is_weekday) ----------------------------------------------------------------------------------------------------------------------------- -- Table Update II: Populate Additional Date Xref Table Fields (Pass II) ----------------------------------------------------------------------------------------------------------------------------- UPDATE DC SET DC.last_day_in_week = DC.first_day_in_week + 6 ,DC.last_day_in_month = DATEADD (MONTH, 1, DC.first_day_in_month) - 1 ,DC.first_day_in_quarter = sqDC.first_day_in_quarter ,DC.last_day_in_quarter = sqDC.last_day_in_quarter ,DC.week_of_month = DATEDIFF (WEEK, DC.first_day_in_month, DC.calendar_date) + 1 ,DC.week_of_quarter = (DC.week_of_year - sqDC.min_week_of_year_in_quarter) + 1 ,DC.is_leap_year = (CASE WHEN DC.calendar_year % 400 = 0 THEN 1 WHEN DC.calendar_year % 100 = 0 THEN 0 WHEN DC.calendar_year % 4 = 0 THEN 1 ELSE 0 END) ,DC.year_week = CONVERT (VARCHAR (4), DC.calendar_year) + RIGHT ('0' + CONVERT (VARCHAR (2), DC.week_of_year), 2) ,DC.year_month = CONVERT (VARCHAR (4), DC.calendar_year) + RIGHT ('0' + CONVERT (VARCHAR (2), DC.calendar_month), 2) ,DC.year_quarter = CONVERT (VARCHAR (4), DC.calendar_year) + 'Q' + CONVERT (VARCHAR (1), DC.calendar_quarter) FROM dbo.date_calendar DC INNER JOIN ( SELECT DC.calendar_year ,DC.calendar_quarter ,MIN (DC.calendar_date) AS first_day_in_quarter ,MAX (DC.calendar_date) AS last_day_in_quarter ,MIN (DC.week_of_year) AS min_week_of_year_in_quarter FROM dbo.date_calendar DC GROUP BY DC.calendar_year ,DC.calendar_quarter ) sqDC ON sqDC.calendar_year = DC.calendar_year AND sqDC.calendar_quarter = DC.calendar_quarter ALTER TABLE dbo.date_calendar ALTER COLUMN last_day_in_week DATETIME NOT NULL ALTER TABLE dbo.date_calendar ALTER COLUMN last_day_in_month DATETIME NOT NULL ALTER TABLE dbo.date_calendar ALTER COLUMN first_day_in_quarter DATETIME NOT NULL ALTER TABLE dbo.date_calendar ALTER COLUMN last_day_in_quarter DATETIME NOT NULL ALTER TABLE dbo.date_calendar ALTER COLUMN week_of_month INT NOT NULL ALTER TABLE dbo.date_calendar ALTER COLUMN week_of_quarter INT NOT NULL ALTER TABLE dbo.date_calendar ALTER COLUMN is_leap_year INT NOT NULL ALTER TABLE dbo.date_calendar ALTER COLUMN year_week VARCHAR (6) NOT NULL ALTER TABLE dbo.date_calendar ALTER COLUMN year_month VARCHAR (6) NOT NULL ALTER TABLE dbo.date_calendar ALTER COLUMN year_quarter VARCHAR (6) NOT NULL CREATE NONCLUSTERED INDEX IX_date_calendar_last_day_in_week ON dbo.date_calendar (last_day_in_week) CREATE NONCLUSTERED INDEX IX_date_calendar_year_month ON dbo.date_calendar (year_month) CREATE NONCLUSTERED INDEX IX_date_calendar_year_quarter ON dbo.date_calendar (year_quarter) ----------------------------------------------------------------------------------------------------------------------------- -- Table Update III: Populate Additional Date Xref Table Fields (Pass III) ----------------------------------------------------------------------------------------------------------------------------- UPDATE DC SET DC.is_last_day_in_month = (CASE WHEN DC.last_day_in_month = DC.calendar_date THEN 1 ELSE 0 END) ,DC.is_last_day_in_quarter = (CASE WHEN DC.last_day_in_quarter = DC.calendar_date THEN 1 ELSE 0 END) ,DC.days_in_month = DATEPART (DAY, DC.last_day_in_month) ,DC.weekdays_in_month = sqDC1.weekdays_in_month ,DC.days_in_quarter = DATEDIFF (DAY, DC.first_day_in_quarter, DC.last_day_in_quarter) + 1 ,DC.quarter_days_remaining = DATEDIFF (DAY, DC.calendar_date, DC.last_day_in_quarter) ,DC.weekdays_in_quarter = sqDC2.weekdays_in_quarter ,DC.year_days_remaining = (365 + DC.is_leap_year) - DC.day_of_year FROM dbo.date_calendar DC INNER JOIN ( SELECT DC.year_month ,SUM (DC.is_weekday) AS weekdays_in_month FROM dbo.date_calendar DC GROUP BY DC.year_month ) sqDC1 ON sqDC1.year_month = DC.year_month INNER JOIN ( SELECT DC.year_quarter ,SUM (DC.is_weekday) AS weekdays_in_quarter FROM dbo.date_calendar DC GROUP BY DC.year_quarter ) sqDC2 ON sqDC2.year_quarter = DC.year_quarter ALTER TABLE dbo.date_calendar ALTER COLUMN is_last_day_in_month INT NOT NULL ALTER TABLE dbo.date_calendar ALTER COLUMN is_last_day_in_quarter INT NOT NULL ALTER TABLE dbo.date_calendar ALTER COLUMN days_in_month INT NOT NULL ALTER TABLE dbo.date_calendar ALTER COLUMN weekdays_in_month INT NOT NULL ALTER TABLE dbo.date_calendar ALTER COLUMN days_in_quarter INT NOT NULL ALTER TABLE dbo.date_calendar ALTER COLUMN quarter_days_remaining INT NOT NULL ALTER TABLE dbo.date_calendar ALTER COLUMN weekdays_in_quarter INT NOT NULL ALTER TABLE dbo.date_calendar ALTER COLUMN year_days_remaining INT NOT NULL ----------------------------------------------------------------------------------------------------------------------------- -- Table Update IV: Populate Additional Date Xref Table Fields (Pass IV) ----------------------------------------------------------------------------------------------------------------------------- UPDATE DC SET DC.month_weekdays_remaining = DC.weekdays_in_month - sqDC.month_weekdays_remaining_subtraction ,DC.quarter_weekdays_remaining = DC.weekdays_in_quarter - sqDC.quarter_weekdays_remaining_subtraction FROM dbo.date_calendar DC INNER JOIN ( SELECT DC.calendar_date ,ROW_NUMBER () OVER ( PARTITION BY DC.year_month ORDER BY DC.calendar_date ) AS month_weekdays_remaining_subtraction ,ROW_NUMBER () OVER ( PARTITION BY DC.year_quarter ORDER BY DC.calendar_date ) AS quarter_weekdays_remaining_subtraction FROM dbo.date_calendar DC WHERE DC.is_weekday = 1 ) sqDC ON sqDC.calendar_date = DC.calendar_date ----------------------------------------------------------------------------------------------------------------------------- -- Table Update V: Populate Additional Date Xref Table Fields (Pass V) ----------------------------------------------------------------------------------------------------------------------------- UPDATE DC SET DC.month_weekdays_remaining = (CASE WHEN DC1.calendar_month = DC.calendar_month AND DC1.month_weekdays_remaining IS NOT NULL THEN DC1.month_weekdays_remaining WHEN DC2.calendar_month = DC.calendar_month AND DC2.month_weekdays_remaining IS NOT NULL THEN DC2.month_weekdays_remaining ELSE DC.weekdays_in_month END) ,DC.quarter_weekdays_remaining = (CASE WHEN DC1.calendar_quarter = DC.calendar_quarter AND DC1.quarter_weekdays_remaining IS NOT NULL THEN DC1.quarter_weekdays_remaining WHEN DC2.calendar_quarter = DC.calendar_quarter AND DC2.quarter_weekdays_remaining IS NOT NULL THEN DC2.quarter_weekdays_remaining ELSE DC.weekdays_in_quarter END) FROM dbo.date_calendar DC LEFT JOIN dbo.date_calendar DC1 ON DATEADD (DAY, 1, DC1.calendar_date) = DC.calendar_date LEFT JOIN dbo.date_calendar DC2 ON DATEADD (DAY, 2, DC2.calendar_date) = DC.calendar_date WHERE DC.month_weekdays_remaining IS NULL ALTER TABLE dbo.date_calendar ALTER COLUMN month_weekdays_remaining INT NOT NULL ALTER TABLE dbo.date_calendar ALTER COLUMN quarter_weekdays_remaining INT NOT NULL ----------------------------------------------------------------------------------------------------------------------------- -- Table Update VI: Populate Additional Date Xref Table Fields (Pass VI) ----------------------------------------------------------------------------------------------------------------------------- UPDATE DC SET DC.is_week_in_same_month = sqDC.is_week_in_same_month ,DC.month_days_remaining = DC.days_in_month - DC.calendar_day ,DC.month_weekdays_completed = DC.weekdays_in_month - DC.month_weekdays_remaining ,DC.quarter_days_completed = DC.days_in_quarter - DC.quarter_days_remaining ,DC.quarter_weekdays_completed = DC.weekdays_in_quarter - DC.quarter_weekdays_remaining ,DC.month_day_name_instance = sqDC.month_day_name_instance ,DC.quarter_day_name_instance = sqDC.quarter_day_name_instance ,DC.year_day_name_instance = sqDC.year_day_name_instance FROM dbo.date_calendar DC INNER JOIN ( SELECT DC.calendar_date ,(CASE WHEN DATEDIFF (MONTH, DC.first_day_in_week, DC.last_day_in_week) = 0 THEN 1 ELSE 0 END) AS is_week_in_same_month ,ROW_NUMBER () OVER ( PARTITION BY DC.year_month ,DC.day_name ORDER BY DC.calendar_date ) AS month_day_name_instance ,ROW_NUMBER () OVER ( PARTITION BY DC.year_quarter ,DC.day_name ORDER BY DC.calendar_date ) AS quarter_day_name_instance ,ROW_NUMBER () OVER ( PARTITION BY DC.calendar_year ,DC.day_name ORDER BY DC.calendar_date ) AS year_day_name_instance FROM dbo.date_calendar DC ) sqDC ON sqDC.calendar_date = DC.calendar_date ALTER TABLE dbo.date_calendar ALTER COLUMN is_week_in_same_month INT NOT NULL ALTER TABLE dbo.date_calendar ALTER COLUMN month_days_remaining INT NOT NULL ALTER TABLE dbo.date_calendar ALTER COLUMN month_weekdays_completed INT NOT NULL ALTER TABLE dbo.date_calendar ALTER COLUMN quarter_days_completed INT NOT NULL ALTER TABLE dbo.date_calendar ALTER COLUMN quarter_weekdays_completed INT NOT NULL ALTER TABLE dbo.date_calendar ALTER COLUMN month_day_name_instance INT NOT NULL ALTER TABLE dbo.date_calendar ALTER COLUMN quarter_day_name_instance INT NOT NULL ALTER TABLE dbo.date_calendar ALTER COLUMN year_day_name_instance INT NOT NULL ----------------------------------------------------------------------------------------------------------------------------- -- Main Query: Final Display / Output ----------------------------------------------------------------------------------------------------------------------------- SELECT DC.* FROM dbo.date_calendar DC ORDER BY DC.calendar_date

Rate