Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Microsoft SQL Server: Programming FAQ

Date and time Tips and tricks

Holiday Calendar Calculator by donutman
Posted: 25 Apr 04 (Edited 1 Jul 04)

   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]

CREATE function dbo.Chanukah (@Yr as int)
returns datetime
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

CREATE FUNCTION dbo.Easter (@Yr as int)
RETURNS datetime
   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.*/

CREATE FUNCTION dbo.GetHolidayDates (@HolidayKey AS int, @StartDate AS datetime, @EndDate AS datetime)
RETURNS @HolidayTable TABLE (HolidayKey int, HolidayDate datetime)
   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
         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
      DELETE @HolidayTable WHERE HolidayDate<@StartDate OR HolidayDate>@EndDate OR HolidayKey<>@HolidayKey AND @OffsetKey<>0

CREATE FUNCTION dbo.OEaster (@Yr as int)
RETURNS datetime
   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.*/

CREATE function dbo.Passover(@Yr int)
returns datetime
   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
         set @Mo=8
         set @Day=@Day-153
   IF @Day>122
         set @Mo=7
         set @Day=@Day-122
   IF @Day>92
         set @Mo=6
         set @Day=@Day-92
   IF @Day>61
         set @Mo=5
         set @Day=@Day-61
   IF @Day>31
         set @Mo=4
         set @Day=@Day-31
   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.*/

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

CREATE function dbo.TuBishvat (@Yr as int)
returns datetime
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

CREATE FUNCTION dbo.YomHaAtzmaut (@Yr as int)
RETURNS datetime
--The "rule" for this date isn't always observered! In 2004 the holiday was observed on 4/27 instead of 4/26!  
   Declare @Date as datetime
   IF @Yr=2004
      set @Date=cast('2004-04-27' as datetime)
      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
The row data for the HolidayDef table is listed here.

 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"

   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

Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close