By: Tim Cullen | 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
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
About the author
Tim Cullen has been working in the IT industry since 2003 and currently works as a SQL Server Reports Developer.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
