TSQL Function to Determine Holidays in SQL Server
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).
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.
- 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
About the author
View all my tips