Holiday Calendar Calculator

In this FAQ, I'll develop a user-defined function (GetHolidayDates) that returns a table of holidays and their dates. The three input parameters of the UDF are: the holiday primary key value, the start date and the end date. If you enter a 0 for the primary key you get all holidays between the two dates. Holidays include the following types: the common secular holidays, most Christian & Jewish holidays and misc. dates like the beginning and ending of Daylight Savings Time, etc.
In order to facilitate the calculations, a Holiday Definition table (HolidayDef) is required. A comma-separated list of 56 rows of data (1 row per holiday) is included here. Hopefully, you can import it after a cut and paste. And finally, in order to calculate the more "complicated" religious holidays, six other user-defined functions are necessary.
I'm not a fan of UDF's because of the performance hit resulting from their use in set operations, but for this use they are extremely helpful. They make the code concise and clear. On my server, the function returns 100 years worth of holidays in 2 sec.
Before I begin, I want to acknowledge the source of the algorithms for Passover, Easter and the Orthodox Easter. Remy Landau has translated the Passover algorithm first published by Carl Friedrich Gauss (1777-1855) into computer Basic code that I ported to SQL. The other two holidays used algorithms developed by J.M. Oudin in 1940. The Easter algorithm appears to be good only from 1754 to 3400.
One more point, there are a few caveats (besides the obvious caveat emptor). The functions do not work prior to 1753 (1754 in 1 case) because the SQL datetime functions do not work prior to 1753. When applied to negative numbers, the SQL function for modulo doesn't work for this purpose. Rendering years prior to about 4000 BC suspect. If necessary, the serious reader could overcome those difficulties. However, I believe the functions do work for the "meaningful" future ignoring the fact that all holidays definitions are somewhat arbitrary and can be changed and that the Gregorian calendar's rule for leap years will eventually require a new tweak in the 4th or 5th millennium.
Here is the script for the HolidayDef table and all of the functions required to calculate the holidays:
[green]
CREATE TABLE [dbo].[HolidayDef] (
[HolidayKey] [int] NOT NULL ,
[OffsetKey] [int] NOT NULL ,
[Type] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FixedMonth] [int] NOT NULL ,
[FixedDay] [int] NOT NULL ,
[DayOfWeek] [int] NOT NULL ,
[WeekOfMonth] [int] NOT NULL ,
[Adjustment] [int] NOT NULL ,
[HolidayName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY]
GO

CREATE function dbo.Chanukah (@Yr as int)
returns datetime
AS
Begin
return case datediff(dd,dbo.Passover(@Yr),dbo.Passover(@Yr+1))
when 355 then dateadd(dd,246,dbo.Passover(@Yr))
when 385 then dateadd(dd,246,dbo.Passover(@Yr))
else dateadd(dd,245,dbo.Passover(@Yr)) end
END
GO

CREATE FUNCTION dbo.Easter (@Yr as int)
RETURNS datetime
AS
BEGIN
Declare @Cent int, @I int, @J int, @K int, @Metonic int, @EMo int, @EDay int
Set @Cent=@Yr/100
Set @Metonic=@Yr % 19
Set @K=(@Cent-17)/25
Set @I=(@Cent-@Cent/4-(@Cent-@K)/3+19*@Metonic+15) % 30
Set @I=@I-(@I/28)*(1-(@I/28)*(29/(@I+1))*((21-@Metonic)/11))
Set @J=(@Yr+@Yr/4+@I+2-@Cent+@Cent/4) % 7
Set @EMo=3+(@I-@J+40)/44
Set @EDay=@I-@J+28-31*(@EMo/4)
Return cast(str(@EMo)+'/'+str(@EDay)+'/'+str(@Yr) as datetime)
/*This algorithm is from the work done by JM Oudin in 1940 and is accurate from year 1754 to 3400.*/
END
GO

CREATE FUNCTION dbo.GetHolidayDates (@HolidayKey AS int, @StartDate AS datetime, @EndDate AS datetime)
RETURNS @HolidayTable TABLE (HolidayKey int, HolidayDate datetime)
AS BEGIN
DECLARE @Yr int, @EndYr int, @OffsetKey int
SET @OffsetKey=isnull((SELECT OffsetKey FROM HolidayDef WHERE HolidayKey=@HolidayKey),0)
SET @Yr=year(@StartDate) SET @EndYr=year(@EndDate)
IF @Yr>@EndYr RETURN
WHILE @Yr<=@EndYr
BEGIN
IF @HolidayKey=0 OR @HolidayKey=15 OR @OffsetKey=15
INSERT INTO @HolidayTable
SELECT 15,dbo.Passover(@Yr)
IF @HolidayKey=0 OR @HolidayKey=18 OR @OffsetKey=18
INSERT INTO @HolidayTable
SELECT 18,dbo.Easter(@Yr)
IF @HolidayKey=0 OR @HolidayKey=19 OR @OffsetKey=19
INSERT INTO @HolidayTable
SELECT 19,dbEaster(@Yr)
IF @HolidayKey=0 OR @HolidayKey=45 OR @OffsetKey=45
INSERT INTO @HolidayTable
SELECT 45,dbo.Chanukah(@Yr)
IF @HolidayKey=0 OR @HolidayKey=54 OR @OffsetKey=54
INSERT INTO @HolidayTable
SELECT 54,dbo.TuBishvat(@Yr)
IF @HolidayKey=0 OR @HolidayKey=55 OR @OffsetKey=55
INSERT INTO @HolidayTable
SELECT 55,dbo.YomHaAtzmaut(@Yr)
IF @HolidayKey=0 OR @HolidayKey=56 OR @OffsetKey=56
INSERT INTO @HolidayTable
SELECT 56,dbo.TishaBAv(@Yr)
INSERT INTO @HolidayTable
SELECT HolidayKey, cast(str(FixedMonth)+'/'+str(FixedDay)+'/'+str(@Yr) AS datetime)
FROM HolidayDef WHERE type='F' AND (@HolidayKey=0 OR @HolidayKey=HolidayKey)
INSERT INTO @HolidayTable
SELECT HolidayKey, cast(str(FixedMonth)+'/'+str((7+DayOfWeek-datepart(dw,cast(str(FixedMonth)+'/01/'+str(@Yr) AS datetime)))%7+1)+'/'+str(@Yr) AS datetime)+(WeekOfMonth-1)*7+Adjustment
FROM HolidayDef WHERE type='M' AND (@HolidayKey=0 OR @HolidayKey=HolidayKey)
INSERT INTO @HolidayTable
SELECT H1.HolidayKey, dateadd(dd,H1.Adjustment,HolidayDate)
FROM HolidayDef H1 INNER JOIN HolidayDef H2 ON (H1.OffsetKey=H2.HolidayKey)
INNER JOIN @HolidayTable HT ON (HT.HolidayKey=H1.OffsetKey AND year(HolidayDate)=@Yr)
WHERE H1.Type='O' AND (@HolidayKey=0 OR @HolidayKey=H1.HolidayKey)
SET @Yr=@Yr+1
END
DELETE @HolidayTable WHERE HolidayDate<@StartDate OR HolidayDate>@EndDate OR HolidayKey<>@HolidayKey AND @OffsetKey<>0
RETURN
END
GO

CREATE FUNCTION dbEaster (@Yr as int)
RETURNS datetime
AS
BEGIN
Declare @I int, @J int, @Metonic int, @EMo int, @EDay int, @LeapAdj int
Set @LeapAdj=@Yr/100-@Yr/400-2
Set @Metonic=@Yr % 19
Set @I=(19*@Metonic+15) % 30
Set @J=(@Yr+@Yr/4+@I) % 7
Set @EMo=3+(@I-@J+40)/44
Set @EDay=@I-@J+28-31*(@EMo/4)
Return DateAdd(dd,@LeapAdj,cast(str(@EMo)+'/'+str(@EDay)+'/'+str(@Yr) as datetime))
/*This algorithm is based upon work done by JM Oudin in 1940.*/
End
GO

CREATE function dbo.Passover(@Yr int)
returns datetime
AS
BEGIN
Declare @HYear int, @Matonic int, @LeapException int, @Leap int, @DOW int, @Century int
Declare @fDay float(20), @fFracDay float(20)
Declare @Mo int, @Day int
Set @HYear=@Yr+3760
Set @Matonic=(12*@HYear+17) % 19
Set @Leap=@HYear % 4
Set @fDay=32+4343/98496.+@Matonic+@Matonic*(272953/492480.)+@Leap/4.
Set @fDay=@fDay-@HYear*(313/98496.)
Set @fFracDay=@fDay-FLOOR(@fDay)
Set @DOW=cast (3*@HYear+5*@Leap+FLOOR(@fDay)+5 as int) % 7
IF @DOW=2 or @DOW=4 or @DOW=6
set @fDay=@fDay+1
IF @DOW=1 and @Matonic>6 and @fFracDay>=1367/2160.
set @fDay=@fDay+2
IF @DOW=0 and @Matonic>11 and @fFracDay>=23269/25920.
set @fDay=@fDay+1
Set @Century=FLOOR(@Yr/100.)
Set @LeapException=FLOOR((3*@Century-5)/4.)
IF @Yr>1582
set @fDay=@fDay+@LeapException
Set @Day=FLOOR(@fDay)
Set @Mo=3
IF @Day>153
Begin
set @Mo=8
set @Day=@Day-153
End
IF @Day>122
Begin
set @Mo=7
set @Day=@Day-122
End
IF @Day>92
Begin
set @Mo=6
set @Day=@Day-92
End
IF @Day>61
Begin
set @Mo=5
set @Day=@Day-61
End
IF @Day>31
Begin
set @Mo=4
set @Day=@Day-31
End
return cast(str(@Mo)+'/'+str(@Day)+'/'+str(@Yr) as datetime)
/* Based on mathematical algorithms first devised by the German mathematician Carl Friedrich Gauss (1777-1855). I have used the date of Passover to determine most of the other Jewish holidays.*/
END
GO

CREATE FUNCTION dbo.TishaBAv (@Yr as int)
RETURNS datetime
AS
BEGIN
return case datepart(weekday,dbo.Passover(@Yr))
when 7 then dateadd(dd,113,dbo.Passover(@Yr))
else dateadd(dd,112,dbo.Passover(@Yr)) end
END
GO

CREATE function dbo.TuBishvat (@Yr as int)
returns datetime
AS
Begin
return case when datediff(dd,dbo.Passover(@Yr-1),dbo.Passover(@Yr))>355
then dateadd(dd,-89,dbo.Passover(@Yr))
else dateadd(dd,-59,dbo.Passover(@Yr)) end
END
GO

CREATE FUNCTION dbo.YomHaAtzmaut (@Yr as int)
RETURNS datetime
AS
--The "rule" for this date isn't always observered! In 2004 the holiday was observed on 4/27 instead of 4/26!
BEGIN
Declare @Date as datetime
IF @Yr=2004
set @Date=cast('2004-04-27' as datetime)
else
set @Date= case datepart(weekday,dbo.Passover(@Yr))
when 1 then dateadd(dd,18,dbo.Passover(@Yr))
when 7 then dateadd(dd,19,dbo.Passover(@Yr))
else dateadd(dd,20,dbo.Passover(@Yr)) end
return @Date
END
GO
[/green]**************************************************************
The row data for the HolidayDef table is listed here.
[tt][green]
1, 0,"F", 1, 1,0,0, 0,"New Year's Day"
2, 0,"M", 1, 0,2,3, 0,"Martin Luther King Jr's BD (Observed)"
3, 0,"F", 2, 2,0,0, 0,"Ground Hog Day"
4, 0,"F", 2,12,0,0, 0,"Lincoln's Birthday"
5, 0,"F", 2,14,0,0, 0,"Valentine's Day"
6, 0,"M", 2, 0,2,3, 0,"President's Day"
7,18,"O", 0, 0,0,0,-47,"Paczki Day (Mardi Gras)"
8,18,"O", 0, 0,0,0,-46,"Ash Wednesday"
9, 0,"F", 2,22,0,0, 0,"Washington's Birthday"
10,15,"O", 0, 0,0,0,-30,"Purim"
11, 0,"F", 3,17,0,0, 0,"St. Patrick's Day"
12, 0,"F", 3,19,0,0, 0,"St. Joseph's Day"
13,18,"O", 0, 0,0,0,-14,"Passion Sunday"
14,18,"O", 0, 0,0,0, -7,"Palm Sunday"
15, 0,"S", 0, 0,0,0, 0,"Passover"
16,18,"O", 0, 0,0,0, -2,"Good Friday"
17, 0,"M", 4, 0,1,1, 0,"Daylight Savings Begins"
18, 0,"S", 0, 0,0,0, 0,"Easter Sunday"
19, 0,"S", 0, 0,0,0, 0,"Orthodox Easter"
20, 0,"M", 5, 0,7,1, -10,"Administrative Professionals Day"
21, 0,"F", 4,22,0,0, 0,"Earth Day"
22, 0,"M", 5, 0,1,2, 0,"Mother's Day"
23, 0,"M", 5, 0,7,3, 0,"Armed Forces Day"
24, 0,"F", 5,31,0,0, 0,"Memorial Day"
25, 0,"F", 6,14,0,0, 0,"Flag Day"
26, 0,"M", 6, 0,1,3, 0,"Father's Day"
27,18,"O", 0, 0,0,0, 49,"Pentecost"
28, 0,"F", 7, 4,0,0, 0,"Independence Day"
29, 0,"M", 9, 0,2,1, 0,"Labor Day"
30,15,"O", 0, 0,0,0,163,"Rosh Hashanah"
31, 0,"M", 9, 0,1,2, 0,"Grandparents Day"
32,15,"O", 0, 0,0,0,172,"Yom Kippur"
33,18,"O", 0, 0,0,0, 39,"Ascension Day"
34, 0,"F",10, 9,0,0, 0,"Leif Erikson Day"
35, 0,"M",10, 0,1,2, 0,"National Children's Day"
36, 0,"M",10, 0,3,2, 0,"Columbus Day (Traditional)"
37, 0,"F",10,16,0,0, 0,"Boss's Day"
38, 0,"M",10, 0,7,3, 0,"Sweetest Day"
39, 0,"M",11, 0,1,1, -7,"Daylight Savings Ends"
40, 0,"F",10,31,0,0, 0,"Halloween"
41, 0,"F",11, 1,0,0, 0,"All Saint's Day"
42, 0,"M",11, 0,2,1, 1,"Election Day"
43, 0,"F",11,11,0,0, 0,"Veterans Day"
44, 0,"M",11, 0,5,4, 0,"Thanksgiving Day"
45, 0,"S", 0, 0,0,0, 0,"Chanukah"
46,18,"O", 0, 0,0,0, 56,"Trinity Sunday"
47, 0,"F",12,25,0,0, 0,"Christmas Day"
48,15,"O", 0, 0,0,0,177,"Sukkot"
49,15,"O", 0, 0,0,0,184,"Shemini Atzeret"
50,15,"O", 0, 0,0,0,185,"Simhat Torah (outside Isreal)"
51, 0,"F", 3,19,0,0, 0,"St. Josephs Day"
52,15,"O", 0, 0,0,0, 33,"Lag B'Omar"
53,15,"O", 0, 0,0,0, 50,"Shavuot"
54, 0,"S", 0, 0,0,0, 0,"Tu Bishvat"
55, 0,"S", 0, 0,0,0, 0,"Yom HaAtzma'ut"
56, 0,"S", 0, 0,0,0, 0,"Tisha B'Av"
[/green][/tt]
To disable the calculation of holidays that you do not want, just change the field "type" to "X" in the appropriate row. If you disable one of the type "S" holidays, then you have to disable all the holidays that are based upon that holiday and you must disable the use of the appropriate function within the GetHolidayDates function (above). You can add your own holidays by adding rows to the table. This can be useful if you have a local event that your business needs to keep track of; perhaps the first school day? The column names should make it easy to understand how the table ôworksö.

I recommend that you run GetHolidayDates only once (after you turn off the holidays you don't want as described above) in order to create a custom HolidayTable for the date range that you are likely to use. Then run a join between your custom table and the HolidayDef table created above in order to list and further filter your holidays.

You can run the GetHolidayDates function like this:
[green]
Set @HolidayKey=0
Set @StartDate=cast('2004-01-01' as datetime)
Set @EndDate=cast('2004-12-31' as datetime)
Select * from dbo.GetHolidayDates(@HolidayKey,@StartDate, @EndDate)
[/green]
which should return all the holidays for 2004 or you could
[green]
Set @HolidayKey=18
Set @StartDate=cast('1800-01-01' as datetime)
Set @EndDate=cast('2100-01-01' as datetime)
Select * from dbo.GetHolidayDates(@HolidayKey, @StartDate, @EndDate)
[/green]
which should return all the dates of Easter for years 1800 to 2099.
Hope you find this useful.
-Karl Schmitt
Deerfield Bakery