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: 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,dbo.OEaster(@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 dbo.OEaster (@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
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: 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)
which should return all the holidays for 2004 or you could 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)
which should return all the dates of Easter for years 1800 to 2099. Hope you find this useful. -Karl Schmitt Deerfield Bakery