Ask about nearly any kind of SQL-based date calculation in the newsgroups, and you'll likely get responses that include use of an auxiliary calendar table. It's a really good idea, and something I highly recommend to anyone who has to do date-based calculations on the server. For those who do dimensional modeling, you'll probably notice the auxiliary calendar table closely resembles a Time dimension with one-day intervals. I won't get into too much detail on it, since ASPFAQ and the newsgroups cover it well.
What I do want to talk about is calculating holidays, which can be important when populating your auxiliary calendar table. When you create an auxiliary calendar table you may need to perform calculations based on business days, for instance; or you may need to schedule activities before, after, or even during certain holidays. There are two types of holidays that I'll address:
- Fixed holidays that fall on the same day every year
- Floating holidays for which the date can change from year to year
Fixed holidays include holidays that always fall on the same day each year. Some examples of fixed holidays include Christmas (always December 25 in the West), Canada Day (always July 1), U.S. Independence Day (always July 4). These are fairly easy to detect and set in your auxiliary calendar table. Just look for the static month + day combinations in your table and update it accordingly.
Floating holidays are far more interesting and difficult to calculate. Take Easter. Easter actually has two definitions. The popular definition is:
- "Easter Day is the first Sunday after the full moon that occurs next after the vernal equinox"
This is not the exact ecclesiastical definition though. The full moon here is not the astronomical full moon but an "ecclesiastical moon" that's determined through precalculated lookup tables. The ecclesiastical rules are:
- Easter falls on the first Sunday following the first ecclesiastical full moon that occurs on or after the day of the vernal equinox
- This particular ecclesiastical full moon is the 14th day of a tabular lunation (new moon)
- The vernal equinox is fixed as March 21
I'm not going to recount the long and painful history leading to the modern definition of Easter--but the details can be found on Wikipedia if you're interested. The main point is that Easter calculation is fairly complex. The udf_CalculateEaster function below accepts the year (integer) as a parameter and returns the date of Easter for that year (datetime). Notice the large number of calculations required to make this function work:
CREATE FUNCTION dbo.udf_CalculateEaster
DECLARE @Date DATETIME,
SELECT @n = @Year - 19 * (@Year / 19),
@c = @Year / 100,
@k = (@c - 17) / 25,
@i = @c - @c / 4 - (@c - @k) / 3 + 19 * @n + 15,
@i = @i - 30 * (@i / 30),
@i = @i - (@i / 28) * (1 - (@i / 28) * (29 / (@i + 1)) * ((21 - @n) / 11)),
@j = @Year + @Year / 4 + @i + 2 - @c + @c / 4,
@j = @j - 7 * (@j / 7),
@l = @i - @j,
@m = 3 + (@l + 40) / 44,
@d = @l + 28 - 31 * (@m / 4),
@Date = CAST
CAST(@Year AS CHAR(4)) +
'0' + CAST
@m AS VARCHAR(2)
@d AS VARCHAR(2)
) AS DATETIME
If you want the date for Easter 2010, just run a query like the following:
SELECT dbo.udf_CalculateEaster (2010);
The result is 2010-04-04.
Another somewhat daunting type of floating holiday is the type that is based on the Nth weekday of the month. These holidays are defined using terms like "the 4th Thursday of November" (U.S. Thanksgiving), "the 3rd Monday in January" (U.S. Martin Luther King Day), or even "the *last* Monday in May" (U.S. Memorial Day). These floating holidays are not as hard to calculate as Easter, but still a little tricky (particularly Memorial Day).
To calculate these dates we need a function like udf_nthWeekDay below. This function accepts 4 parameters:
@n = integer that represents which instance of the weekday you're looking for. This would be the 4 in "4th Thursday in November".
@weekDay = 3-character day of week ("SUN" = Sunday, "MON" = Monday, etc.) I used character abbreviations instead of numbers just to make it a little easier.
@year = integer year for which to calculate
@month = integer month for which to calculate (1 = January, 2 = February, etc.)
As you can see below, the Nth weekday calculation is pretty simple.
CREATE FUNCTION dbo.udf_nthWeekDay
DECLARE @date DATETIME,
SELECT @wd = CASE @weekDay
WHEN 'SUN' THEN 1
WHEN 'MON' THEN 2
WHEN 'TUE' THEN 3
WHEN 'WED' THEN 4
WHEN 'THU' THEN 5
WHEN 'FRI' THEN 6
WHEN 'SAT' THEN 7
@date = CAST
CAST(@year AS VARCHAR(4)) +
'0' + CAST
@month AS VARCHAR(2)
'01' AS DATETIME
@dow = DATEPART(dw, @date),
@offset = @wd - @dow,
@date = DATEADD(day, @offset + (@n - CASE WHEN @offset >= 0 THEN 1 ELSE 0 END) * 7, @date);
To get the date for Thanksgiving 2024 you could call the function like this:
The result is 2024-11-28. Now for a holiday like U.S. Memorial Day ("the last Monday in May") the calculation is a little trickier. You have no idea up front how many Mondays there are in any given May. As an example, in 2010 and 2011 there are 5 Mondays in May; in 2012 and 2013 there are only 4. You could try to figure out the number of Mondays in May of the given year, but that's way too much work.
There's one thing we know about the last Monday in May with absolute certainty: after the last Monday in May, the next Monday we encounter will *always* be the first Monday in June. So the easier way to figure out the last Monday in May is to calculate the first Monday in June and then back into it (subtract 7 days), like this:
By calculating the date of the first Monday in June 2012, we can then subtract 7 days to get the date of the last Monday of May: 2012-05-28.
You can use the functions to calculate holidays for your auxiliary calendar table. I covered U.S. federal (and some other national) holidays in this one, but it can be extended to cover other national, state, and local holidays.