US Federal Holidays (SQL)

Introduction

I am unfortunate enough to have to be tasked with writing QlikView scripts instead of being a real programmer, and sometimes, the scripts (I hesitate to call them "apps", because that's not what they are at all, however, Qlik calls them that - what a bunch of bozos) require a "calendar" table that is generated from scratch, and that covers a specific date range. I developed a stored procedure that generates said calendar table, and one of the fields returned in that table is a flag that indicates whether the date represented by a given row is a US federal holiday.

The C# version of this tip is here: US Federal Holidays Using (C#)

Background

Up to this point, we had been using a Qlikview script to generate this calendar table. Since Qlikview is the performance pig you might already assume that it is, I decided that I'd create a SQL-based solution and just load the data into Qlikview from SQL.

Using the code

To give fair warning, I am not what I would call a SQL wizard, but I know how to get around. With that in mind, here's the scalar function I came up with that feeds my stored procedure. I make frequent use of the DATEPART, DATEADD, DATENAME functions available in SQL at the top of the function to ease typing, and follow that up with merely checking values to determine whether or not the specified date is a federal holiday.

There are essentially three types of holidays - holidays that fall on an nth day of the week (such as MLK day, President's Day, etc), and holidays that occur on a certain day of the month but that are adjusted when that day occurs on a weekend (such as New Year's Day, Christmas, etc). For no other reason than "it just made sense to do it that way", I check for each holiday in the order it appears in the year. Using the function below as a template, you can add additional holidays that are applicable to your project(s). Comments indicate which holiday for which I am checking.

NOTE: Your namespace probably won't be Reference_Tables, so remember to change that to fit your schema.

USE [Reference_Tables]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =======================================================================================
-- Author:		jms
-- Create date: 18 JAN 2017
-- Description:	Determines if the specified date is a US federal holiday.
-- =======================================================================================
CREATE FUNCTION [dbo].[fn_IsHoliday]
(
    @date  date
)
RETURNS bit
AS
BEGIN
    -- for ease of typing
    DECLARE @year  int = DATEPART(YEAR, @date);
    DECLARE @month int = DATEPART(MONTH,@date);
    DECLARE @day   int = DATEPART(DAY, @date);
    DECLARE @dayName varchar(12) = DATENAME(DW, @date );

    DECLARE @nthWeekDay int = ceiling(@day / 7.0);
    DECLARE @isThursday bit = CASE WHEN @dayName LIKE 'Thursday' THEN 1 ELSE 0 END;
    DECLARE @isFriday   bit = CASE WHEN @dayName LIKE 'Friday' THEN 1 ELSE 0 END;
    DECLARE @isSaturday bit = CASE WHEN @dayName LIKE 'Saturday' THEN 1 ELSE 0 END;
    DECLARE @isSunday   bit = CASE WHEN @dayName LIKE 'Sunday' THEN 1 ELSE 0 END;
    DECLARE @isMonday   bit = CASE WHEN @dayName LIKE 'Monday' THEN 1 ELSE 0 END;
    DECLARE @isWeekend  bit = CASE WHEN @isSaturday = 1 OR @isSunday = 1 THEN 1 ELSE 0 END;
     
    ---- New Years Day
    if (@month = 12 AND @day = 31 AND @isFriday=1) return 1;
    if (@month = 1 AND @day = 1 AND @isWeekend=0) return 1;
    if (@month = 1 AND @day = 2 AND @isMonday=1) return 1;

    ---- MLK day
    if (@month = 1 AND @isMonday = 1 AND @nthWeekDay = 3) return 1;

    ------ President’s Day ( 3rd Monday in February )
    if (@month = 2 AND @isMonday = 1 AND @nthWeekDay = 3) return 1;

    ------ Memorial Day ( Last Monday in May )
    if (@month = 5 AND @isMonday = 1 AND DATEPART(MONTH, DATEADD(DAY, 7, @Date)) = 6) return 1;

    ------ Independence Day ( July 4 )
    if (@month = 7 AND @day = 3 AND @isFriday = 1) return 1;
    if (@month = 7 AND @day = 4 AND @isWeekend = 0) return 1;
    if (@month = 7 AND @day = 5 AND @isMonday = 1) return 1;

    ------ Labor Day ( 1st Monday in September )
    if (@month = 9 AND @isMonday = 1 AND @nthWeekDay = 1) return 1;

    ------ Columbus Day ( 2nd Monday in October )
    if (@month = 10 AND @isMonday = 1 AND @nthWeekDay = 2) return 1;

    ------ Veteran’s Day ( November 11 )
    if (@month = 11 AND @day = 10 AND @isFriday = 1) return 1;
    if (@month = 11 AND @day = 11 AND @isWeekend = 0) return 1;
    if (@month = 11 AND @day = 12 AND @isMonday = 1) return 1;

    ------ Thanksgiving Day ( 4th Thursday in November )
    if (@month = 11 AND @isThursday = 1 AND @nthWeekDay = 4) return 1;

    ------ Christmas Day ( December 25 )
    if (@month = 12 AND @day = 24 AND @isFriday = 1) return 1;
    if (@month = 12 AND @day = 25 AND @isWeekend = 0) return 1;
    if (@month = 12 AND @day = 25 AND @isMonday = 1) return 1;

    return 0;
END

GO

Usage

You can call a function inside a SQL query like so:

SELECT [ADateValue],
       dbo.fn_IsHoliday([ADateValue]) AS IsFederalHoliday -- this will be a bit type
FROM SomeTable;

Points of Interest

The strongest reason for the people to retain the right to keep and bear arms is, as a last resort, to protect themselves against tyranny in government.

History

01 Feb 2017 - Original submission.