SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Holidays and Calendar Generation

By Paul Hunter, (first published: 2016/02/16)

One of my recurring headaches was to maintain a list of holidays and working days for businesses.  I got tired of "gotta get the holidays loaded" and "why aren't next year's dates in the table?".  My solution was to make the process dynamic.  My only caveat is that this is US, Anglo, and Christian holiday centric. this is where I live, but the concepts are similar for anywhere.  Here's how I solved the problem.

Define Holidays

Most holidays follow certain rules.

  1. The easy ones are those holidays that occur on a specific day of a month.  New Years, Christmas and Independence Day (US) are examples of this.  They occur on specific days and "they float".  So, if the date falls on a Saturday, the holiday will "float" to the preceeding Friday.  If it falls on a Sunday then it will "float" to the following Monday.
  2. There are holidays that occur on a specific day of the week and a specific week of they month.
  3. There are holidays that are eccumenical.  For whatever reason, US securities markets are closed on Good Friday and this holiday follows a recurring but varied cycle.  I'm sure there are others, but this is the one I've encountered.

Armed with that informaiton, I came up with a HolidayDefintion table that contains Country, a common name for the holiday, the month, week and day on which it occurs, if it floats, if it's a federal (government) holiday and if it's a business holiday (i.e. Good Friday is a business but not a federal holiday).  Here's that code..


CREATE TABLE dbo.HolidayDefinition
    (
      CountryCd CHAR(2) NOT NULL
    , Holiday VARCHAR(50) NOT NULL
    , MonthNbr TINYINT NOT NULL
    , WeekNbr TINYINT NOT NULL
    , DayNbr TINYINT NOT NULL
    , IsFloating BIT NOT NULL
        CONSTRAINT DF_HolidayDefinition_IsFloating DEFAULT ( (0) )
    , IsFederal BIT NOT NULL
            CONSTRAINT DF_HolidayDefinition_IsFederal DEFAULT ( (1) )
    , IsBusiness BIT NOT NULL
        CONSTRAINT DF_HolidayDefinition_IsBusiness DEFAULT ( (0) )
    , CONSTRAINT PK_HolidayDefinition PRIMARY KEY NONCLUSTERED
        ( CountryCd ASC, Holiday ASC )
    ); 
GO
CREATE UNIQUE CLUSTERED INDEX UK_HolidayDefinition ON dbo.HolidayDefinition 
    (
    MonthNbr ASC, WeekNbr ASC, DayNbr ASC, CountryCd ASC 
);
GO
ALTER TABLE dbo.HolidayDefinition 
  WITH CHECK ADD CONSTRAINT CK_HolidayDefinition CHECK ((CASE WHEN WeekNbr>(0) 
                                                              THEN CASE WHEN IsFloating=(1) OR DayNbr>(7) 
               THEN (0) 
           ELSE (1) 
END 
 ELSE (1) 
     END=(1)));
GO
ALTER TABLE dbo.HolidayDefinition 
  CHECK CONSTRAINT CK_HolidayDefinition;
GO
ALTER TABLE dbo.HolidayDefinition 
  WITH CHECK ADD CONSTRAINT CK_HolidayDefinition_DayNbr CHECK ((DayNbr>=(1) AND DayNbr<=(31)));
GO
ALTER TABLE dbo.HolidayDefinition 
  CHECK CONSTRAINT CK_HolidayDefinition_DayNbr;
GO
ALTER TABLE dbo.HolidayDefinition
  WITH CHECK ADD CONSTRAINT CK_HolidayDefinition_MonthNbr CHECK ((MonthNbr>=(1) AND MonthNbr<=(12)));
GO
ALTER TABLE dbo.HolidayDefinition
  CHECK CONSTRAINT CK_HolidayDefinition_MonthNbr;
GO
ALTER TABLE dbo.HolidayDefinition
  WITH CHECK ADD CONSTRAINT CK_HolidayDefinition_WeekNbr CHECK ((WeekNbr>=(0) AND WeekNbr<=(5)));
GO
ALTER TABLE dbo.HolidayDefinition
  CHECK CONSTRAINT CK_HolidayDefinition_WeekNbr;
GO

A few rules are enforced by the check constraints, but some aren't.  First let me describe the rules that are enforced with constraints:

  • Either a holday occurs on a specifc day or a day of the week and week of the month.  If it occurs on a specific day then the week number must equal 0, otherwise it will fall on weeks 1 - 4 or 5 for the last (i.e. Memorial Day falls on the last Monday of May whereas Thanksgiving occurs on the 4th Thursday)
  • If WeekNbr = 0 then DayNbr can be any number < 32. (That's right, you can have a February or April 31st if you like - this is the not-checked constraint)
  • If WeekNbr > 0 then DayNbr is the day number coresponding to the day of the week
  • Only holidays falling on a specific day can "float"

Create Definitions

Next you'll need to load holidays into the table.  You can look them up if you like, but here's the statement I use to merge in the new dates:

-- create cte containing the source data to be merged into the target table...
;
WITH    cte_HolidayDefinition
          AS (
               SELECT CountryCd = 'US'
                  , Holiday = 'New Years Day'
                  , MonthNbr = '1'
                  , WeekNbr = '0'
                  , DayNbr = '1'
                  , IsFloating = '1'
                  , IsFederal = '1'
                  , IsBusiness = '1'
               UNION ALL
               SELECT CountryCd = 'US'
                  , Holiday = 'Martin Luther King Day'
                  , MonthNbr = '1'
                  , WeekNbr = '3'
                  , DayNbr = '2'
                  , IsFloating = '0'
                  , IsFederal = '1'
                  , IsBusiness = '1'
               UNION ALL
               SELECT CountryCd = 'US'
                  , Holiday = 'Presidents Day'
                  , MonthNbr = '2'
                  , WeekNbr = '3'
                  , DayNbr = '2'
                  , IsFloating = '0'
                  , IsFederal = '1'
                  , IsBusiness = '1'
               UNION ALL
               SELECT CountryCd = 'US'
                  , Holiday = 'Easter Sunday'
                  , MonthNbr = '4'
                  , WeekNbr = '1'
                  , DayNbr = '1'
                  , IsFloating = '0'
                  , IsFederal = '0'
                  , IsBusiness = '0'
               UNION ALL
               SELECT CountryCd = 'US'
                  , Holiday = 'Good Friday'
                  , MonthNbr = '4'
                  , WeekNbr = '1'
                  , DayNbr = '5'
                  , IsFloating = '0'
                  , IsFederal = '0'
                  , IsBusiness = '0'
               UNION ALL
               SELECT CountryCd = 'US'
                  , Holiday = 'Memorial Day'
                  , MonthNbr = '5'
                  , WeekNbr = '5'
                  , DayNbr = '2'
                  , IsFloating = '0'
                  , IsFederal = '1'
                  , IsBusiness = '1'
               UNION ALL
               SELECT CountryCd = 'US'
                  , Holiday = 'Independence Day'
                  , MonthNbr = '7'
                  , WeekNbr = '0'
                  , DayNbr = '4'
                  , IsFloating = '1'
                  , IsFederal = '1'
                  , IsBusiness = '1'
               UNION ALL
               SELECT CountryCd = 'US'
                  , Holiday = 'Labor Day'
                  , MonthNbr = '9'
                  , WeekNbr = '1'
                  , DayNbr = '2'
                  , IsFloating = '0'
                  , IsFederal = '1'
                  , IsBusiness = '1'
               UNION ALL
               SELECT CountryCd = 'US'
                  , Holiday = 'Columbus Day'
                  , MonthNbr = '10'
                  , WeekNbr = '2'
                  , DayNbr = '2'
                  , IsFloating = '0'
                  , IsFederal = '1'
                  , IsBusiness = '1'
               UNION ALL
               SELECT CountryCd = 'US'
                  , Holiday = 'Veterans Day'
                  , MonthNbr = '11'
                  , WeekNbr = '0'
                  , DayNbr = '11'
                  , IsFloating = '0'
                  , IsFederal = '1'
                  , IsBusiness = '1'
               UNION ALL
               SELECT CountryCd = 'US'
                  , Holiday = 'Thanksgiving Day'
                  , MonthNbr = '11'
                  , WeekNbr = '4'
                  , DayNbr = '5'
                  , IsFloating = '0'
                  , IsFederal = '1'
                  , IsBusiness = '1'
               UNION ALL
               SELECT CountryCd = 'US'
                  , Holiday = 'Black Friday'
                  , MonthNbr = '11'
                  , WeekNbr = '4'
                  , DayNbr = '6'
                  , IsFloating = '0'
                  , IsFederal = '0'
                  , IsBusiness = '0'
               UNION ALL
               SELECT CountryCd = 'US'
                  , Holiday = 'Christmas Day'
                  , MonthNbr = '12'
                  , WeekNbr = '0'
                  , DayNbr = '25'
                  , IsFloating = '1'
                  , IsFederal = '1'
                  , IsBusiness = '1'
             )
    -- using an EXCEPT statement generates the true diferences between the cte source and the target table you want to merge into...
MERGE dbo.HolidayDefinition tgt
USING (
        SELECT CountryCd
              , Holiday
              , MonthNbr
              , WeekNbr
              , DayNbr
              , IsFloating
              , IsFederal
              , IsBusiness
            FROM cte_HolidayDefinition
        EXCEPT -- return only true differences
        SELECT CountryCd
              , Holiday
              , MonthNbr
              , WeekNbr
              , DayNbr
              , IsFloating
              , IsFederal
              , IsBusiness
            FROM dbo.HolidayDefinition
      ) src
ON tgt.CountryCd = src.CountryCd
    AND tgt.MonthNbr = src.MonthNbr
    AND tgt.WeekNbr = src.WeekNbr
    AND tgt.DayNbr = src.DayNbr
WHEN MATCHED THEN
    UPDATE SET CountryCd = src.CountryCd
              , Holiday = src.Holiday
              , MonthNbr = src.MonthNbr
              , WeekNbr = src.WeekNbr
              , DayNbr = src.DayNbr
              , IsFloating = src.IsFloating
              , IsFederal = src.IsFederal
              , IsBusiness = src.IsBusiness
WHEN NOT MATCHED THEN
    INSERT ( CountryCd
           , Holiday
           , MonthNbr
           , WeekNbr
           , DayNbr
           , IsFloating
           , IsFederal
           , IsBusiness
           )
    VALUES ( src.CountryCd
           , src.Holiday
           , src.MonthNbr
           , src.WeekNbr
           , src.DayNbr
           , ISNULL(src.IsFloating, 0)
           , ISNULL(src.IsFederal, 1)
           , ISNULL(src.IsBusiness, 0)
           ) ;
GO
Whew, that was a lot of fun.

Building a Holiday Calendar

Now that we have the holidays defined, we're ready to start using them.  I use them with a simple inline table-valued function.


IF OBJECT_ID(N'dbo.fn_HolidyCalendar', N'IF') IS NULL
    BEGIN
        EXEC sys.sp_executesql N'create function dbo.fn_HolidyCalendar() returns table as return(select 0 as dummy)';
    END;
GO
ALTER FUNCTION dbo.fn_HolidyCalendar
    (
      @CountryCd CHAR(2) = 'US'
    , @Year SMALLINT
    )
RETURNS TABLE
/*
————————————————————————————————————————————————————————————————————————————————————————————————————
              © 2012-15 · NightOwl Development · All rights Reserved
————————————————————————————————————————————————————————————————————————————————————————————————————
Purpose : Builds a dynamic list of holiday dates.
Returns : Returns Holiday date and other information about Holidays for the secified country and year.
Notes   :
History :
   Date Developer  Jira Nbr  Modification
—————————— —————————————— —————————— ————————————————————————————————————————————————————————————
2011-12-14 P. Hunter 0 Object created.
————————————————————————————————————————————————————————————————————————————————————————————————————
*/
AS
RETURN
    SELECT h.CountryCd
          , h.Holiday
          , HolidayOn = CONVERT(DATE, CASE h.WeekNbr
                                        WHEN 0
                                        THEN CASE h.IsFloating
                                               WHEN 0 -- doesn't float
                                               THEN r.HolidayOn
                      -- adjust floating holidays if they fall on Sunday or Saturday
                                               ELSE DATEADD(DAY,
                                                            CASE DATENAME(WEEKDAY,
                                                              r.HolidayOn)
                                                              WHEN 'Sunday'
                                                              THEN 1
                                                              WHEN 'Saturday'
                                                              THEN -1
                                                              ELSE 0
                                                            END, r.HolidayOn)
                                             END
                                        ELSE r.HolidayOn
                                      END)
          , h.MonthNbr
          , h.WeekNbr
          , h.DayNbr
          , h.IsFloating
          , h.IsFederal
          , h.IsBusiness
        FROM dbo.HolidayDefinition h
            CROSS APPLY (
        -- convert the definition to beginning and end of the month dates
  SELECT BOM = CONVERT(DATE, CONVERT(CHAR(4), @Year)
        + RIGHT(CONVERT(CHAR(5), ( 100 * h.MonthNbr ) + 10001), 4))
      , EOM = CASE h.WeekNbr
                WHEN 5
                THEN DATEADD(DAY, -1,
                             DATEADD(MONTH, 1,
                                     CONVERT(DATE, CONVERT(CHAR(4), @Year)
                                     + RIGHT(CONVERT(CHAR(5), ( 100
                                                              * h.MonthNbr )
                                             + 10001), 4))))
              END
      ) m
            CROSS
APPLY (
        -- determine the raw offset days to apply to get to the holiday date
SELECT offset = CASE h.WeekNbr
                  WHEN 0 -- occurs on a specific day so, add this to BOM
                       THEN h.DayNbr - 1
                  WHEN 5 -- occurs on the last specified weekday of the month
                       THEN CASE WHEN DATEPART(WEEKDAY, m.EOM) >= h.DayNbr
                                 THEN -( DATEPART(WEEKDAY, m.EOM) - h.DayNbr )
                                 ELSE -( ( DATEPART(WEEKDAY, m.EOM) - h.DayNbr )
                                         + 7 )
                            END
                  ELSE CASE WHEN h.DayNbr >= DATEPART(WEEKDAY, m.BOM)
                            THEN ( h.DayNbr - DATEPART(WEEKDAY, m.BOM) )
                                 + ( h.WeekNbr - 1 ) * 7
                            ELSE ( h.DayNbr - DATEPART(WEEKDAY, m.BOM) )
                                 + ( h.WeekNbr * 7 )  -- days before/after the first day of the month
                       END
                END
      ) o
            CROSS
APPLY (
        -- create a esitmated holiday date based on the offset and WeekNbr
-- this should be correct, except for floating holidays (WeekNbr = 0).
    SELECT HolidayOn = DATEADD(DAY, o.offset, CASE h.WeekNbr
                                                WHEN 5 THEN m.EOM
                                                ELSE m.BOM
                                              END)
      ) r
        WHERE h.CountryCd = @CountryCd
            AND ( h.IsFederal = 1
                  OR h.IsBusiness = 1
                )
            AND h.Holiday NOT IN ( 'Good Friday', 'Easter Sunday' )
    UNION
ALL
-- retrieve good friday based on the ecumenical calcs for easter sunday
    SELECT h.CountryCd
          , h.Holiday
          , r.HolidayOn
          , r.MonthNbr
          , WeekNbr = 0
          , r.DayNbr
          , h.IsFloating
          , h.IsFederal
          , h.IsBusiness
        FROM (
               SELECT Holiday = 'Good Friday'
                  , HolidayOn = CONVERT(DATE, CONVERT(CHAR(4), @Year)
                    + CASE WHEN e.d > 31 THEN '04'
                           ELSE '03'
                      END + RIGHT(CONVERT(CHAR(3), 100
                                  + CASE WHEN e.d > 31 THEN e.d - 31
                                         ELSE e.d
                                    END - 2), 2))
                  , MonthNbr = CASE WHEN e.d > 31 THEN 4
                                    ELSE 3
                               END
                  , DayNbr = CASE WHEN e.d > 31 THEN e.d - 31
                                  ELSE e.d
                             END
                FROM (
                       SELECT d.g
                          , d.c
                          , d.h
                          , d.i
                          , d.d
                          , IsFalse = CONVERT(BIT, 0)
                        FROM (
                               SELECT c.g
                                  , c.c
                                  , c.h
                                  , c.i
                                  , d = c.i - ( ( @Year + ( @Year / 4 ) + c.i
                                                  + 2 - c.c + ( c.c / 4 ) )
                                                % 7 ) + 28
                                FROM (
                                       SELECT b.g
                                          , b.c
                                          , b.h
                                          , i = ( b.c - ( b.c / 4 ) - ( ( 8
                                                              * b.c + 13 )
                                                              / 25 ) + 19
                                                  * b.g + 15 ) % 30
                                        FROM (
                                               SELECT a.g
                                                  , a.c
                                                  , h = ( a.c - ( a.c / 4 )
                                                          - ( ( 8 * a.c + 13 )
                                                              / 25 ) + 19
                                                          * a.g + 15 ) % 30
                                                FROM (
                                                       SELECT g = @Year % 19
                                                          , c = @Year / 100
                                                     ) a
                                             ) b
                                     ) c
                             ) d
                     ) e
             ) r
            CROSS
APPLY (
        SELECT *
            FROM dbo.HolidayDefinition d
            WHERE d.CountryCd = @CountryCd
                AND d.Holiday = r.Holiday
                AND ( d.IsFederal = 1
                      OR d.IsBusiness = 1
                    )
      ) h;
GO

I don't know how this code wil turn out, but the calcs to find Easter can be found in a lot of places. I used this source and SQLized it. Now all you have to do is get a number table, calculate the number of days and years between a start and end date, and you can spontaneously generate a custom calendar with holidays and business days.  I'm attaching the files for this post.  Holidays.sql has all of the code listed in the post, the Numbers function is the one I use anytime I need a number table and the Calendar function completes the generate of a contiguous calendar with business days and holidays.  I hope they are as useful for you as they've been for me.

 

Resources:

Holidays.sql | dbo.fn_Numbers.sql | dbo.fn_Calendar.sql
Total article views: 4662 | Views in the last 30 days: 7
 
Related Articles
SCRIPT

Holiday List

A table UDF to return a list of holidays for a given year.

FORUM

Holidays

I need to find a way to skip holidays for a scheduled job.

FORUM

Excluding Job Execution On Holidays

Excluding Job Execution On Holidays

ARTICLE

Happy Holidays 2012

Merry Christmas and Happy Holidays from SQLServerCentral

ARTICLE

Creating Holidays and Special Dates

A way to save holiday records that can be created as needed into a search-able calendar table.

Tags
calendar table    
t-sql    
 
Contribute