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

Using SWITCHOFFSET

Doug Kline has a video and T-SQL script around date/time offsets and particularly the SWITCHOFFSET function: — so, before SWITCHOFFSET existed, … SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(),'-05:00') AS [EST the easy way], TODATETIMEOFFSET(DATEADD(HOUR, -5, SYSDATETIMEOFFSET()), '-05:00') AS [EST the hard way] — so, thinking of a DATETIMEOFFSET data type as a complex object — with many different parts: […]

Read More

Daylight Savings Time Calculations In Power BI

Fred Kaffenberger shows us how to convert UTC to local time zones with daylight savings time: Quick tip for DST Refresh Date function Power BI Service. I’ll put the code up front, and explain it below. I’ll also say a bit about how to use it at the end. The United States and other places, like Australia, […]

Read More

Categories

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