I’m the type of developer that invents wheels. Yes, every wheel I design is unique in its own way, and hand-crafted for a specific purpose. And so it has also been with calendar dimensions (typically when I do data warehousing work).

This got me thinking – why not design the mother of all calendar dimensions? One that includes every conceivable calendar and property that I and others could use and re-use. One that could save me a ton of coding, and lessen the burden of having to validate it each and every time?

And that’s how I got started designing my one calendar script to rule them all.

What it is

It’s a collection of inline table value functions that generate different types of calendars, with a number of properties that could be relevant for a calendar dimension. Each function has a unique date column, so you can join the functions you need together in a view or a procedure. The functions are:

• Dates: a plain gregorian calendar.
• Fiscal, annual: a gregorian, year-based calendar where you can define the start of a year, like a corporate fiscal calendar.
• Fiscal, 4-4-5 or 52/53: a week-based calendar where years comprise four quarters of 4+4+5 weeks respectively.
• Indian national calendar
• Persian calendar
• Thai calendar
• Dates of Catholic and Orthodox easter
• Lunar cycle

The installation script creates a schema called “Calendar” (if you don’t already have one), but you could easily change this to suit your own environment.

If you want to download it and take it for a spin, jump straight to the Downloads page.

What’s to come

I’ve also started work on a “holiday calendar”, which allows you to define a set of rule-based, recurring events, like public holidays, tax reporting dates, etc.

But that’s the subject of a future post.

What it isn’t

Some calendars (notably the Islamic and Hebrew calendars) depend on astronomical readings of the lunar cycle. I’ve left those out, as it’s not realistic to deterministically predict how those years will start/end.

I’ve also left out archaic and religious calendars that (to my knowledge) are not used in everyday business. My focus here is on creating a foundation for a calendar dimension, for business intelligence, banking, trading or other work-related use.

Help needed & appreciated!

I would love it if you could help me validate some of these results! This is particularly true for things that are out of my comfort zone, like Persian or Indian dates, lunar cycles, etc. If you find an error, or have a suggestion on how to improve something, please let me know in the comments!

Documentation

Calendar.Dates(@from, @to)

This function returns a set of gregorian dates between the two parameters @from and @to.

SELECT *
FROM Calendar.Dates({d '1900-01-01'}, {d '2099-12-31'});

Parameters

• @from (date): the starting date of the dataset
• @to (date): the ending date of the dataset

Output columns

• i, bigint: the number of days from the @from date. You could use this column to join with the “i” column of the other calendar functions
• Date, date
• Year, smallint: four-digit year number
• Quarter, tinyint: quarter, 1-4
• Year_quarter, char(7): Combined year and quarter, like “2018 Q3”
• Month, tinyint: Month, 1-12
• Year_month, char(7) :Combined year and month, like “2018-07”
• Day, tinyint: Day of the month, 1-31
• Date_ISO, char(10): ISO formatted date, like “2018-07-31”
• ISO_week_year, smallint: Calendar year of the ISO week, four digits. Same as the calendar year, except when the ISO week belongs to previous or following year. For instance,
  “2017-01-01” is in the last week of 2016 (week 52), and
  “2018-12-31” is in the first week of 2019 (week 1).
• ISO_week, tinyint: 1-53, corresponding to DATEPART(iso_week).
• ISO_year_week, char(8): combined ISO year and ISO week, like “2018 W29”
• ISO_weekday_number, tinyint: monday-based day of the week, 1-7.
• US_week, tinyint: 1-54, corresponding to DATEPART(week)
• US_year_week, char(8): combined calendar year and week, like “2018 W29”
• US_weekday_number, tinyint: sunday-based day of the week, 1-7.
• Weekday_name, nvarchar(30): name of the weekday, based on your locale, equivalent to DATENAME(weekday).
• Day_of_year, smallint: 1-based day of the calendar year. 1 is January 1.
• Day_of_year_30E_360, int: Day of the calendar year, but intended for 30E/360 interest date calculations, so it excludes the 31st of the month and changes the last day of February to the 30th, to create a total of 360 days per year.

Calendar.Fiscal_annual(@from, @to, @financial_year)

Many organisations use a fiscal year for reporting purposes that does not correlate to the calendar year. By far the most common fiscal year starts on a different month than January, but uses periods that still correlate to months.

So, for a fiscal year that starts in May, the first period is May, the second period is June, the third is July, and so on.

SELECT *
FROM Calendar.Fiscal_annual({d '1900-01-01'}, {d '2099-12-31'}, {d '2000-09-01'});

Parameters

• @from (date): the starting date of the dataset
• @to (date): the ending date of the dataset
• @financial_year (date): the start of financial year 1.

Output columns

• i, bigint: the number of days from the @from date. You could use this column to join with the “i” column of other the calendar functions if you want to performance tune a few milliseconds, as this is the internal ordering column.
• Date, date
• Fiscal_year, smallint: Sequential fiscal year number, starting at @fiscal_year. Preceding years will have negative fiscal year numbers.
• Fiscal_year_name, varchar(9): Plaintext name of the year. If the year starts with January 1, the name is the year number, four digits. Otherwise, it’s formatted (first year)/(second year).
• Fiscal_year_start, date: the date of the first day of the fiscal year.
• Fiscal_year_end, date: the date of the last day of the fiscal year.
• Fiscal_quarter, tinyint: quarter of the fiscal year, 1-4.
• Fiscal_year_quarter, varchar(12): the fiscal year name and fiscal quarter, like “2017/2018 Q4”.
• Fiscal_period, tinyint: Fiscal period (month) number, 1-12.
• Fiscal_year_period, varchar(13): the fiscal year name and fiscal period, like “2017/2018 P09”
• Fiscal_week, tinyint: week number of the fiscal year
• Fiscal_year_week, varchar(13): the fiscal year name and fiscal week number, like “2017/2018 W48”
• Day_of_fiscal_year, smallint: sequential day number of fiscal year, starting with 1.
• Day_of_fiscal_quarter, tinyint: sequential day number of fiscal quarter, starting with 1.
• Day_of_fiscal_period, tinyint: sequential day number of fiscal period, starting with 1.

Calendar.Fiscal_4_4_5(@from, @to, @fiscal_year, @weekly_style, @last_day_before_date)

A less common form of fiscal year is the 4-4-5 calendar, also known as the 52/53 calendar. This fiscal calendar is useful for businesses that are cyclic on a weekly basis, rather than an annual one. While the annual fiscal year has varying lengths and a varying number of days and weeks, this week-based fiscal calendar revolves around weeks and groups of weeks.

SELECT *
FROM Calendar.Fiscal_4_4_5({d '1900-01-01'}, {d '2099-12-31'}, {d '2000-09-01'}, '445', 1);

Years, quarters, periods and weeks

The 4-4-5 calendar contains an evenly divisible number of weeks (52 or 53 per year), divided into periods. Each period consists of 4 or 5 periods. Three periods add up to a quarter of 13 weeks, so a year adds up to four quarters. Some years will contain 53 weeks, where an additional “leap week” is added to one of the quarters.

Defining the start date of a year

A 4-4-5 calendar always starts on the same weekday. The ending date of a fiscal year is defined by one of two methods.

For example, assuming a case where the fiscal year starts on a Sunday in the beginning of September:

• @last_day_before_date=1: the last Saturday of August marks the end of the fiscal year.
• @last_day_before_date=0: the Saturday closest to, or on, September 1 marks the end of the fiscal year.

Parameters

• @from (date): the starting date of the dataset
• @to (date): the ending date of the dataset
• @fiscal_year (date): the starting date of fiscal year 1
• @weekly_style (char(3)): how weeks are distributed across quarters. Valid choices are “445”, “454” or “544”, but the function would conceivably handle any combination of numbers that add up to 13.
• @last_day_before_date (bit): Determines if the last day of the fiscal period is the last (weekday of @fiscal_year) before the date of @fiscal_year (1) or the (weekday of @fiscal_year) closest to the date of @fiscal_year (0).

Output columns

The output columns of this function are identical in names and types to those of Calendar.Fiscal_annual().

Calendar.Sunrise_Sunset(@from, @to, @west_long, @north_lat, @altitude)

Returns the approximate times of sunrise, solar noon (when the sun is at its highest point) and sunset, according to the Sunrise Equation.

--- Stockholm, Sweden
DECLARE @lat float=59. +19./60 +46./3600;
DECLARE @lon float=18. + 4./60 + 7./3600,
        @alt float=10;

SELECT *
FROM Calendar.Sunrise_Sunset('2000-01-01', '2029-12-31', @lon, @lat, @alt);

Parameters

• @from (date): the start date of the resultset
• @to (date): the end date of the resultset
• @west_long: longitude as degrees; east of 0° are denoted as positive values, west as negative.
• @north_lat: latitude as degrees; north of the equator as positive values, south of the equator as negative values.
• @altitude: meters above sea level.

Output columns

• i, bigint
• Date, date
• Declination, numeric(6, 3), degrees of declination, the tilt of the earth
• Hour_angle, numeric(6, 3), degrees
• Sunrise_UTC, datetime, sunrise time in UTC time
• Solar_noon_UTC, datetime, solar noon in UTC time
• Sunset_UTC, datetime, sunset time in UTC time

Calendar.Indian(@from, @to)

Returns the Indian national calendar, which builds on 12-month years, starting on March 21/22. Its structure is similar to that of the Persian calendar.

SELECT *
FROM Calendar.Indian({d '1912-01-01'}, {d '2099-12-31'});

Parameters

• @from (date): the starting date of the dataset
• @to (date): the ending date of the dataset

Output columns

• i, bigint
• Date, date
• Indian_year, smallint: the Indian year
• Indian_year_start, date: the gregorian start date of the Indian year
• Indian_year_end, date: the gregorian end date of the Indian year
• Indian_quarter, tinyint: quarter of the Indian year, 1-4
• Indian_year_quarter, char(7): Indian year and quarter, combined, like “1940 Q2”
• Indian_month, tinyint: Indian month number, 1-12
• Indian_month_name, nvarchar(10): the Indian name of the month
• Indian_year_month, char(7): Indian year and month, combined, like “1940-04”
• Indian_month_start, date: gregorian date of the first day of the Indian month
• Indian_month_end, date: gregorian date of the last day of the Indian month
• Day_of_Indian_month, tinyint: the day of the Indian month, 1-31
• Weekday_number, tinyint: Sunday-based weekday
• Day_of_Indian_year, smallint: the day of the Indian year

Calendar.Persian(@from, @to)

The Persian calendar (the Solar Hijri calendar) roughly corresponds in structure to the Indian national calendar. It does feature some slight differences with regards to the start date of the year.

SELECT *
FROM Calendar.Persian({d '1912-01-01'}, {d '2099-12-31'});

Parameters

• @from (date): the starting date of the dataset
• @to (date): the ending date of the dataset

Output columns

• i, int
• Date, date
• Persian_year, smallint: the Persian year
• Persian_year_start, date: gregorian date of the first day of the Persian year
• Persian_year_end, date: gregorian date of the last day of the Persian year
• Persian_quarter, tinyint: the quarter of the Persian date, 1-4
• Persian_year_quarter, char(7): combined Persian year and quarter, like “1397 Q2”
• Persian_month, tinyint: Persian month number, 1-12
• Iranian_month_name, nvarchar(15)
• Iranian_romanized_month_name, nvarchar(12)
• Kurdish_month_name, nvarchar(11)
• Afghan_Persian_month_name, nvarchar(6)
• Afghan_Pashto_month_name, nvarchar(8)
• Persian_year_month, char(7): combined Persian year and month number, like “1397-04”
• Persian_month_start, date: gregorian date of the first day of the Persian month
• Persian_month_end, date: gregorian date of the last day of the Persian month
• Day_of_Persian_month, tinyint: day of the Persian month, 1-31
• Weekday_number, tinyint: weekday number, Saturday being the first day of the week.
• Day_of_Persian_year, smallint: day of the Persian year

Calendar.Thai(@from, @to)

The Thai calendar was most recently updated in 1912 and 1941 (CE), when Thailand adopted the gregorian calendar, with the exception of the year number.

Until 1912 (CE), the calendar year started on April 11 (as of 1822, as far as I could tell). As of 1912, it was redefined to start on April 1, and as of 1941, Thailand has adopted the gregorian month cycle that starts on January 1. Thailand still uses an offset year number, though.

This Thai calendar does not return dates before April of 1912.

SELECT *
FROM Calendar.Thai({d '1912-04-01'}, {d '2099-12-31'});

Parameters

• @from (date): the starting date of the dataset
• @to (date): the ending date of the dataset

Output columns

• i, bigint
• Date, date
• Thai_year, smallint: the year number of the Thai date
• Thai_year_start, date: gregorian date of the first day of the Thai year
• Thai_year_end, date: gregorian date of the last day of the Thai year
• Thai_quarter, tinyint: quarter of the Thai date, 1-4
• Thai_year_quarter, char(7): combined Thai year and quarter, like “2561 Q3”
• Thai_month, tinyint: the month of the Thai date
• Thai_month_name, nvarchar(10): Thai month name
• Thai_month_name_short, nvarchar(5): abbreviated Thai month name
• Thai_year_month, char(7): combined Thai year and month, like “2561-07”
• Thai_month_start, date: gregorian date of the first day of the Thai month
• Thai_month_end, date: gregorian date of the last day of the Thai month
• Day_of_Thai_month, int: day of the Thai month
• Weekday_number, tinyint: weekday number, Sunday being the first day of the week
• Day_of_Thai_year, smallint: day of the Thai year

Calendar.Easter(@from, @to)

Easter dates are dependent on lunar cycles, but this function uses generally accepted methods to calculate the occurrence of both the western and eastern dates of easter Sunday. Several christian holidays (including national holidays in many countries) are based on the date of easter.

SELECT [Year], Catholic_Easter_Sunday, Orthodox_Easter_Sunday
FROM Calendar.Easter({d '1900-01-01'}, {d '2099-12-31'});

Parameters

• @from (date): the starting date of the dataset
• @to (date): the ending date of the dataset

Output columns

• Year, smallint: gregorian year number
• Catholic_Easter_Sunday, date: the date of the Catholic (western) easter Sunday
• Orthodox_Easter_Sunday, date: the date of the Orthodox (eastern) easter Sunday

Calendar.Lunar_cycle(@from, @to)

Returns the lunar cycle and phase of each date.

SELECT *
FROM Calendar.Lunar_cycle({d '1900-01-01'}, {d '2099-12-31'});

Parameters

• @from (date): the starting date of the dataset
• @to (date): the ending date of the dataset

Output columns

• i, bigint
• Date, date: gregorian date
• Lunar_cycle_start, date: the first date of the current lunar cycle
• Lunar_cycle, smallint: a sequential number of lunar cycles
• Day_of_lunar_cycle, bigint: the day of the current lunar cycle. The first day of the lunar cycle is the new moon
• Phase_description, varchar(42): one of seven descriptions of the lunar phase

EDIT: As of November 2018, this script is on GitHub, so you can fork your own copy, make improvements to it, and send me a pull request!