week – sqlsunday.com

The SQL Server Calendar project

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.

A function to calculate recurring dates

When you’re using Microsoft Outlook, or pretty much any other personal information manager, you can create calendar appointments that are “recurring”, i.e. you can have them repeat at a defined frequency. This, however may not only apply to your project meeting appointments, but also to some database solution. I decided to give it a go at building a table value function that returns a list of dates, based on a given set of parameters.

Weeks and weekday parts in dates

Here are two common pitfalls that you may encounter when it comes to working with dates, specifically with week numbers and weekdays. The first is to mistake week numbers in SQL Server for ISO week numbers (which are the weeknumbers used in Sweden, for instance). The other is to make assumptions about which day of the week is the first. The good news is that these are relatively easy fixes.