Creating The Ultimate Calendar Table

Daniel Hutmacher has started to create a comprehensive calendar table:

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

I was going to jokingly be shocked that this list didn’t include the Hebrew or Islamic calendars, but then Daniel had to ruin my fun by explaining why not.  Check it out and when you’re ready to give it a try, head over to his downloads page.

Related Posts

SARGability and Date Functions

Erik Darling shows why you don’t want to use YEAR() or MONTH() in the WHERE clause when querying a large table: If you’ve been query tuning for a while, you probably know about SARGability, and that wrapping columns in functions is generally a bad idea. But just like there are slightly different rules for CAST and […]

Read More

Extended Event Duration Units of Measure

Emanuele Meazzo shows how we can find out whether that duration column is milliseconds or microseconds: Even if I use Extended Events almost every day, I always forget the unit of measure of each duration counter, since they’re basically arbitrary; Seconds, milliseconds, microseconds? Whatever, it depends on the dev that implemented that specific counter. That’s […]

Read More

Categories

July 2018
MTWTFSS
« Jun Aug »
 1
2345678
9101112131415
16171819202122
23242526272829
3031