TSQL Function to Determine Holidays in SQL Server

By:   |   Comments (11)   |   Related: > Dates

Problem

While working on a project recently I was asked to develop a mechanism that would provide the dates for state and federal holidays in a given year. Since this project deals with all states and territories of the United States, the list had to be comprehensive and the client asked that this list be in the form of a SQL query calculated on-the-fly, rather than a static list. To account for all holidays, I grouped each one into one of three categories.  First are static holidays.  These holidays have the same date every year, Like New Year's Day (01/01) and Independence Day (07/04).  Second are dynamic holidays.  These holidays occur on a specific day of week in a specific week and month. Examples would be Thanksgiving (fourth Thursday of November) and Dr. Martin Luther King, Jr. Day (third Monday of January).  Third are other holidays.  These holidays have a pattern unlike the other two. Examples would be Memorial Day (last Monday in May) and Mardi Gras (40 days prior to Easter not including Sundays).

Solution

Two holiday calculations were most interesting: Good Friday and Mardi Gras. Since both of these holidays are based on the number of days from Easter, I had to find the calculation for Easter. The calculation of Easter Sunday is based on the vernal equinox each year, and thankfully the Astronomical Applications Department of the U.S Naval Observatory has an equation that calculates it:

```c = y / 100
n = y - 19 * (y / 19)
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 = y + y / 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)
```

In this equation, y is the year, m is the month of occurrence, and d is the day of occurrence. Since Easter is a date that other holiday calculations are based on, I decided to wrap the equation into a function:

```CREATE FUNCTION [dbo].[fn_EasterSundayByYear]
(@Year char(4))
RETURNS smalldatetime
AS
BEGIN
--http://aa.usno.navy.mil/faq/docs/easter.php
declare
@c int
, @n int
, @k int
, @i int
, @j int
, @l int
, @m int
, @d int
, @Easter datetime

set @c = (@Year / 100)
set @n = @Year - 19 * (@Year / 19)
set @k = (@c - 17) / 25
set @i = @c - @c / 4 - ( @c - @k) / 3 + 19 * @n + 15
set @i = @i - 30 * ( @i / 30 )
set @i = @i - (@i / 28) * (1 - (@i / 28) * (29 / (@i + 1)) * ((21 - @n) / 11))
set @j = @Year + @Year / 4 + @i + 2 - @c + @c / 4
set @j = @j - 7 * (@j / 7)
set @l = @i - @j
set @m = 3 + (@l + 40) / 44
set @d = @l + 28 - 31 * ( @m / 4 )

set @Easter = (select right('0' + convert(varchar(2),@m),2) + '/' + right('0' + convert(varchar(2),@d),2) + '/' + convert(char(4),@Year))

return @Easter
END
```

From there calculating the Good Friday date is easy. Using the DATEADD function we subtract two days from Easter to get the date for Good Friday:

Calculating the Mardi Gras date for a given year is not as easy as Good Friday, so I created another function that performs the calculation:

```CREATE FUNCTION [dbo].[fn_MardiGras]
(@Year char(4))
RETURNS smalldatetime
AS
BEGIN
declare @Easter datetime, @Count tinyint, @MardiGras datetime
set @Easter = (select DOCPM.dbo.fn_EasterSundayByYear(@Year))
set @Count = 0
set @MardiGras = @Easter

while @Count < 40
BEGIN
if datepart(dw,@MardiGras) = 1
BEGIN
set @Count = @Count
END
else
BEGIN
set @Count = @Count + 1
END

CONTINUE
END

RETURN @mardiGras
END
```

Essentially we are using a WHILE statement to count down the days to 40, excluding the Sundays. During the WHILE statement, if the day of week of the currently set date is a Sunday we leave the counter the same, otherwise we add one to the counter. These functions and calculations are used as part of a larger function that calculates all holidays in a given year.

Next Steps

Tim Cullen has been working in the IT industry since 2003 and currently works as a SQL Server Reports Developer.

View all my tips

 Monday, December 5, 2022 - 9:15:46 AM - Idde Back To Top (90748) For handling dates also after 2099:DECLARE @YEAR char(4) = '2100' SELECT CONVERT(DATETIME, CONVERT(CHAR(4), @year) + '-' + RIGHT('00' + CONVERT(VARCHAR, CONVERT(INT, ((((19 * (@year % 19) + (CONVERT(INT, @year / 100)) - (CONVERT(INT, (CONVERT(INT, @year / 100)) / 4)) - (CONVERT(INT, ((CONVERT(INT, @year / 100)) - (CONVERT(INT, (CONVERT(INT, @year / 100)) + 8) / 25) + 1) / 3)) + 15) % 30)) + (((32 + 2 * ((CONVERT(INT, @year / 100)) % 4) + 2 * (CONVERT(INT, (@year % 100) / 4)) - (((19 * (@year % 19) + (CONVERT(INT, @year / 100)) - (CONVERT(INT, (CONVERT(INT, @year / 100)) / 4)) - (CONVERT(INT, ((CONVERT(INT, @year / 100)) - (CONVERT(INT, (CONVERT(INT, @year / 100)) + 8) / 25) + 1) / 3)) + 15) % 30)) - ((@year % 100) % 4)) % 7)) - 7 * (CONVERT(INT, ((@year % 19) + 11 * (((19 * (@year % 19) + (CONVERT(INT, @year / 100)) - (CONVERT(INT, (CONVERT(INT, @year / 100)) / 4)) - (CONVERT(INT, ((CONVERT(INT, @year / 100)) - (CONVERT(INT, (CONVERT(INT, @year / 100)) + 8) / 25) + 1) / 3)) + 15) % 30)) + 22 * (((32 + 2 * ((CONVERT(INT, @year / 100)) % 4) + 2 * (CONVERT(INT, (@year % 100) / 4)) - (((19 * (@year % 19) + (CONVERT(INT, @year / 100)) - (CONVERT(INT, (CONVERT(INT, @year / 100)) / 4)) - (CONVERT(INT, ((CONVERT(INT, @year / 100)) - (CONVERT(INT, (CONVERT(INT, @year / 100)) + 8) / 25) + 1) / 3)) + 15) % 30)) - ((@year % 100) % 4)) % 7))) / 451)) + 114) / 31)), 2) + '-' + RIGHT('00' + CONVERT(VARCHAR, (((((19 * (@year % 19) + (CONVERT(INT, @year / 100)) - (CONVERT(INT, (CONVERT(INT, @year / 100)) / 4)) - (CONVERT(INT, ((CONVERT(INT, @year / 100)) - (CONVERT(INT, (CONVERT(INT, @year / 100)) + 8) / 25) + 1) / 3)) + 15) % 30)) + (((32 + 2 * ((CONVERT(INT, @year / 100)) % 4) + 2 * (CONVERT(INT, (@year % 100) / 4)) - (((19 * (@year % 19) + (CONVERT(INT, @year / 100)) - (CONVERT(INT, (CONVERT(INT, @year / 100)) / 4)) - (CONVERT(INT, ((CONVERT(INT, @year / 100)) - (CONVERT(INT, (CONVERT(INT, @year / 100)) + 8) / 25) + 1) / 3)) + 15) % 30)) - ((@year % 100) % 4)) % 7)) - 7 * (CONVERT(INT, ((@year % 19) + 11 * (((19 * (@year % 19) + (CONVERT(INT, @year / 100)) - (CONVERT(INT, (CONVERT(INT, @year / 100)) / 4)) - (CONVERT(INT, ((CONVERT(INT, @year / 100)) - (CONVERT(INT, (CONVERT(INT, @year / 100)) + 8) / 25) + 1) / 3)) + 15) % 30)) + 22 * (((32 + 2 * ((CONVERT(INT, @year / 100)) % 4) + 2 * (CONVERT(INT, (@year % 100) / 4)) - (((19 * (@year % 19) + (CONVERT(INT, @year / 100)) - (CONVERT(INT, (CONVERT(INT, @year / 100)) / 4)) - (CONVERT(INT, ((CONVERT(INT, @year / 100)) - (CONVERT(INT, (CONVERT(INT, @year / 100)) + 8) / 25) + 1) / 3)) + 15) % 30)) - ((@year % 100) % 4)) % 7))) / 451)) + 114) % 31) + 1), 2))

 Friday, January 2, 2015 - 9:19:14 AM - Joe Orost Back To Top (35812) Note: Marti Gras or Shrove Tuesday is exactly 47 days before Easter.  There is no reason to loop in order to compute it.   Ref: https://en.wikipedia.org/wiki/Shrove_Tuesday

 Tuesday, January 19, 2010 - 2:31:03 PM - jcelko Back To Top (4735) Was this Easter the GREEK ORTHODOX or ROMAN CATHOLIC  date?  You can download lists of various religous holidays for the next few decades if you do a little Googling first. This is one of many reasons I tell people to use a Calendar tables and not to do calculations in proprietary T-SQL code.

 Friday, July 25, 2008 - 7:45:23 AM - Starnamer Back To Top (1504) I just checked that my method of calculating Easter cannot have been based on the same code as Tim's as mine only works from 1900 to 2099 (for 2100 and later the date returned is Easter Saturday; I suspect this is due to not compensating for 2100 being a leap year). Also, it seems that mine gets the wrong date in 1954, 1981, 2049 and possibly 2076. Since the range of interest is actually the years around now, (1998-present) this hasn't been a problem, but I've fixed it by replacing the initial '21' in the calcalation by case @year when 1954 then 14 when 1981 then 14 when 2049 then 14 when 2076 then 14 else 21 end Apart from that, Tim's function and my one-liner produce the same date for all other years from 1900 to 2099. Derek.

 Friday, July 25, 2008 - 7:14:36 AM - Starnamer Back To Top (1503) I had to do this a couple of years ago for holidays in several countries of the world. In this case, so far, I have defined 8 types of holiday. Due to changing regulations, these are all defined as valid for a range of years, since, for example, in the UK the Late Spring Bank Holiday normally falls on the last Monday in May, but in 2002 it was moved to Tuesday 4th June (Monday 3rd June 2002 was the Queen's Silver Jubilee Holiday). The types are: 1. Fixed, e.g. Independence Day, Christmas Day in many countries. 2. Days relative to Easter, e.g. Pentecost, Ascension Day 3. Specific day of week in specific week of week in month. Contrary to Tim's view, this can be written to handle both cases of like Thanksgiving, 4th Thursday in November and Memorial day, last Monday in May, by writing it as the first specified weekday following a specific date in the month. Thanksgiving is the Thursday after 21st November and Memorial Day is the Monday after 24th May. 4. Fixed, but moves to following Monday if it's a weekend, e.g. in the UK if Christmas Day falls on Saturday or Sunday then Monday is a holiday. 5. Special case for Boxing Day (the day after Christmas Day in the UK); if it falls on a Saturday, it makes the following Monday a holiday and if it falls on a Sunday then it makes the following Tuesday a holiday (because Christmas Day has already made Monday a holiday!) 6. Fixed date in Hijri (Muslim) calendar, e.g. first day of fasting. Fortunately, SQL Server has a builtin Hijri calendar based on the Kuwaiti algorithm, so this just means doing some conversions using date style number 131 (Hijri Calendar) 7. Days relative the Greek Orthodox Easter. This is similar to 2, but using a different calculation for Easter. 8. Relative to the previous winter solstice (Chinese holiday of Qing Ming Jie). Fortunately, the date of the winter solstice is fixed, so this is really just compensating for leap years.  I've actually encountered an 9th type, which is relative to the phase of the moon (normally, relative to the day of the full/new moon), but rather than start coding astronomical calculations, I've currently entered these as fixed holidays valid only for specific years.  The other thing I did was to rewrite the Easter calculation as a single line. In the CASE on type of holiday (0 to 7): when 1 -- days relative to Easter (e.g. Whitmonday is Easter Sunday + 50 days) then dateadd(d,(21 + ((19 * (@year % 19) + 24 ) % 30) + ((2 * (@year % 4) + 4 * (@year % 7) + 6 * ((19 * (@year % 19) + 24) % 30) + 5) % 7)) + [MonthDayOffset],convert(char(4),@year)+'0301') I beleive it's actually the same calculation that Tim used, just drastically revised. [MonthDayOffset] is a field which stores either the date of a fixed holiday, e.g. 704 for Independence Day, or an offset, e.g. (-2) for Good Friday, which is 2 days before Easter. The core of the Greek Orthodox Easter calculation is simply the Sunday following dateadd(d,(((@year % 19) * 19 + 16) % 30),convert(char(4),@year)+'0402') The table-valued function is about 200 lines long and, obviously, has an associated table to drive it which currently has 330 records used to define the holidays for about 30 countries. Hopefully, there are no more types to be defined!  Derek.

 Monday, July 21, 2008 - 1:02:31 PM - sgmunson Back To Top (1476) Ok, thanks.   With that info confirmed, then I have to say that the original article has a small technical problem, in that Fat Tuesday is the 41st non-Sunday day going backwards from Easter, instead of the 40th, unless somehow you don't count that 1st Saturday (the day before Easter), and I can't see what logic one would use to not count it.   Anyone? Steve (aka sgmunson)

 Monday, July 21, 2008 - 12:48:59 PM - mjswart Back To Top (1475) With the assumption that mardi gras to easter is a fixed number of days (which it is). I subtracted the easter date from the mardi gras date as they landed this year in 2008. That's how I got 47. And yes, I know quite a bit about Mardi Gras. Mardi Gras doesn't just begin on Fat Tuesday. Mardi Gras is Fat Tuesday.

 Monday, July 21, 2008 - 9:22:10 AM - sgmunson Back To Top (1473) Just so I understand it correctly, why wouldn't it be minus 46 days?  Doesn't the Saturday immediately prior to Easter (Easter Eve, if you will) count as the 1st day of those 40 days?   Oh wait... Mardi Gras begins on Fat Tuesday, right ?   If it's always that way, then indeed, 47 is the correct number.   Can someone more familiar with the specifics of Mardi Gras please confirm?   Thanks! Steve (aka sgmunson)

 Friday, July 11, 2008 - 10:42:27 AM - jrandall Back To Top (1410) Nice article!  You noted that the two functions you show are part of a larger function to determine all holidays.  Would you post the larger function?Thanks

 Friday, July 11, 2008 - 9:19:57 AM - timothyrcullen Back To Top (1409) Good information, Michael.  Thanks for pointing that out!

 Thursday, July 10, 2008 - 6:16:25 AM - mjswart Back To Top (1391) I don't see how Good Friday is any trickier than Mardi Gras. The number of Sundays in Lent is constant and so we can avoid the while loop and do the same thing we did for Good Friday: declare @MardiGras datetime, @Year char(4)set @Year = '2009'set @MardiGras =     DATEADD(day, -47    ,(select DOCPM.dbo.fn_EasterSundayByYear(@Year)))Michael Swart