Click here to Skip to main content
15,867,594 members
Articles / Database Development
Tip/Trick

US Federal Holidays (SQL)

Rate me:
Please Sign up or sign in to vote.
4.52/5 (6 votes)
31 Jan 2017CPOL2 min read 23.5K   4   3
A scalar function to determine if a given DateTime is one of the ten US federal holidays, using SQL Server.

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.

SQL
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:

SQL
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) Paddedwall Software
United States United States
I've been paid as a programmer since 1982 with experience in Pascal, and C++ (both self-taught), and began writing Windows programs in 1991 using Visual C++ and MFC. In the 2nd half of 2007, I started writing C# Windows Forms and ASP.Net applications, and have since done WPF, Silverlight, WCF, web services, and Windows services.

My weakest point is that my moments of clarity are too brief to hold a meaningful conversation that requires more than 30 seconds to complete. Thankfully, grunts of agreement are all that is required to conduct most discussions without committing to any particular belief system.

Comments and Discussions

 
QuestionJuneteenth Pin
#realJSOP26-Mar-24 7:40
mve#realJSOP26-Mar-24 7:40 
QuestionChristmas on a Sunday Suggestion Pin
Mike Meinz2-Feb-17 6:57
Mike Meinz2-Feb-17 6:57 
QuestionI don't understand Pin
#realJSOP1-Feb-17 6:32
mve#realJSOP1-Feb-17 6:32 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.