TSQL Function to Determine Holidays in SQL Server

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 
      set @MardiGras = dateadd(day,-1,@MardiGras) 
      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

• Review the equation used by the U.S. Naval Observatory to calculate Easter Sunday
• Read about creating functions in SQL Server 2000 and SQL Server 2005
• Read about using the DATEADD and WHILE functions

Tim Cullen

Tim has been working in the IT industry since 2003. After spending a few years as an “IT Generalist”, his career focus turned to SQL Server and application development. He currently works as an IT Specialist for a Federal agency in Maryland, where he focuses on SQL Server administration and providing guidance to teams on SQL Server performance issues. When not working, he enjoys playing golf, playing bass guitar, and exercising.

• MSSQLTips Awards: Trendsetter (25+ tips)