Identify All Holiday Dates By Year using TSQL

Question

I did a lot of searching for an easy solution to dynamically identify U.S. federal holidays by year. I wasn't able to find much information for the trickier holidays. Holidays like New Year's Day or Independence Day are easy to program as they are static. However, some are more difficult to identify programmatically such as Presidents' Day (3rd Monday in February) or Thanksgiving (4th Thursday in November).

Answer 1

I know this is an old question, but we have a sweet Scalar-valued Function that returns 1 if it's a holiday and 0 if it is not. We do have to manually add Observed Dates - We use the https://www.timeanddate.com/holidays/us/ to get the observed dates.

First, create the function:

USE [DATABASE]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[IsHoliday](@date as DATETIME)

RETURNS bit
AS

BEGIN
    if @date is not null
    begin
        -- JAN
        IF Month(@date)=1 AND day(@date)=1 return 1 -- New Years Day
        IF Month(@date)=1 AND DATEPART(weekday, @date)=2 and day(@date)>14 and day(@date)<=21  return 1 -- Martin Luther King, Jr. Day
        -- FEB
        IF Month(@date)=2 AND DATEPART(weekday, @date)=1 and day(@date)<=7  return 1 -- Super Bowl Sunday
        IF Month(@date)=2 AND day(@date)=14 return 1 -- Valentine's Day
        IF Month(@date)=2 AND DATEPART(weekday, @date)=2 and day(@date)>14 and day(@date)<=21  return 1 -- Presidents' Day
        -- MAR
        IF Month(@date)=3 AND day(@date)=17 return 1 -- St Patrick's Day
        -- MAY
        IF Month(@date)=5 AND DATEPART(weekday, @date)=1 and day(@date)>7 and day(@date)<=14  return 1 -- Mother's day
        IF Month(@date)=5 AND DATEPART(weekday, @date)=2 and day(@date)>24 and day(@date)<=31 return 1 --Memorial Day
        -- JUN
        IF Month(@date)=6 AND day(@date)=19 return 1 -- Juneteenth
        IF Month(@date)=6 AND DATEPART(weekday, @date)=2 and day(@date)>14 and day(@date)<=21 return 1 --Father's Day
        -- JUL
        IF Month(@date)=7 AND day(@date)=4 return 1 -- July 4th
        -- SEP
        IF Month(@date)=9 AND DATEPART(weekday, @date)=2 and day(@date)<=7  return 1--Labor Day
        -- OCT
        IF Month(@date)=10 AND DATEPART(weekday, @date)=2 and day(@date)>7 and day(@date)<=14 return 1 --Columbus Day
        IF Month(@date)=10 AND day(@date)=31 return 1 -- Halloween
        -- NOV
        IF Month(@date)=11 AND day(@date)=11 return 1 -- Veteran's Day
        IF Month(@date)=11 AND DATEPART(weekday, @date)=5 and day(@date)>21 and day(@date)<=28 return 1 --Thanksgiving
        -- DEC
        IF Month(@date)=12 AND day(@date)=24 return 1 -- Christmas Eve
        IF Month(@date)=12 AND day(@date)=25 return 1 -- Christmas Day
        IF Month(@date)=12 AND day(@date)=31 return 1 -- NYE    

        -- Observed Dates
        if month(@date)=1 AND day(@date)=2 AND year(@date)=2017 return 1 -- New Years Day observed for 2017
        if month(@date)=11 AND day(@date)=10 AND year(@date)=2017 return 1 -- Veteran's Day observed for 2017
        if month(@date)=11 AND day(@date)=12 AND year(@date)=2018 return 1 -- Veteran's Day observed for 2018
        if month(@date)=7 AND day(@date)=3 AND year(@date)=2020 return 1 -- 4th of July Observed for 2021
        if month(@date)=6 AND day(@date)=18 AND year(@date)=2021 return 1 -- Juneteenth observed for 2021
        if month(@date)=7 AND day(@date)=5 AND year(@date)=2021 return 1 -- 4th of July Observed for 2021
        if month(@date)=6 AND day(@date)=20 AND year(@date)=2022 return 1 -- Juneteenth observed for 2022
        if month(@date)=12 AND day(@date)=26 AND year(@date)=2022 return 1 -- Christmas Day observed for 2022
        if month(@date)=1 AND day(@date)=2 AND year(@date)=2023 return 1 -- New Years Day observed for 2023
        if month(@date)=11 AND day(@date)=10 AND year(@date)=2023 return 1 -- Veteran's Day observed for 2023
        if month(@date)=7 AND day(@date)=3 AND year(@date)=2026 return 1 -- 4th of July Observed for 2026
        if month(@date)=6 AND day(@date)=18 AND year(@date)=2027 return 1 -- Juneteenth observed for 2027
        if month(@date)=7 AND day(@date)=5 AND year(@date)=2027 return 1 -- 4th of July Observed for 2027
        if month(@date)=11 AND day(@date)=10 AND year(@date)=2028 return 1 -- Veteran's Day observed for 2028
        if month(@date)=11 AND day(@date)=12 AND year(@date)=2029 return 1 -- Veteran's Day observed for 2029
    end
    return 0
END
GO

Then call the function:

SELECT dbo.[IsHoliday](GETDATE())

SELECT dbo.[IsHoliday]('2021-07-05 09:20:51.270')

Answer 2

There's some math you can use to make your SQL life easier e.g. 3rd Monday in February mathematically has to be between the 15th and the 21st (the earliest 3rd Monday has 14 days before it; the latest 3rd Monday can have no more than 20 days before it). If you have a tally table, it will be pretty easy to find all the dates. Here's how you can do it for president's day

with t1 as 
(SELECT 1 num
   FROM  (VALUES (1),(1),(1),(1),(1),(1),(1),(1)) subTable(n)
),
TallyTable as
(
  SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY (SELECT 1)) n
     FROM t1 a
        CROSS JOIN t1 b
        CROSS JOIN t1 c
        CROSS JOIN t1 d
        CROSS JOIN t1 e
        CROSS JOIN t1 f
),
DateTable as
(
  SELECT DateAdd(day,n,'1/1/2018') DateValue
  FROM TallyTable
)
SELECT *
  FROM DateTable DT
  WHERE DatePart(month,DT.DateValue) = 2 --February
    AND DatePart(dw,DT.DateValue) = 2 --Monday
    AND DatePart(day,DT.DateValue) BETWEEN 15 AND 21; --Day is between 15 and 21

Answer 3

Here is the solution I came up with.

I created a table variable to store the entire years dates:

DECLARE @DateTable TABLE
(
    dtDate DATE,
    dtMonth VARCHAR(10),
    dtDayName VARCHAR(10),
    dtDayRank INT
);

Populated first 3 columns of the @DateTable:

DECLARE @Year CHAR(4), @CurrentDate DATE
SET @Year = '2018'
SET @CurrentDate = CAST(@Year + '0101' AS DATE)

WHILE @CurrentDate <= CAST(@Year + '1231' AS DATE)
BEGIN
    INSERT INTO @DateTable (dtDate, dtMonth, dtDayName)
        VALUES (@CurrentDate, DATENAME(mm, @CurrentDate), DATENAME(dw, @CurrentDate))
    SET @CurrentDate = DATEADD(dd, 1, @CurrentDate)
END;

Once I had the table populated, I ranked the rows and updated the table:

UPDATE @DateTable
SET dtDayRank = rankdates.DayRank
FROM @DateTable datatable
    INNER JOIN (
        SELECT
            dtDate,
            DayRank = RANK() OVER (PARTITION BY dtMonth, dtDayName ORDER BY dtDate)  -- rank each DayOfWeek in order
        FROM @DateTable
    ) rankdates ON datatable.dtDate = rankdates.dtDate;

Sample Output from @DateTable

Once I had the @DateTable populated, I could use logic to identify specific days.

    SELECT 
        HolidayName = 'Presidents'' Day',
        ObservedDayOfWeek = dtDayName,
        HolidayObservedDate = dtDate
    FROM @DateTable
    WHERE dtMonth = 'February'
    AND dtDayName = 'Monday'
    AND dtDayRank = 3 

    SELECT 
        HolidayName = 'Thanksgiving Day',
        ObservedDayOfWeek = dtDayName,
        HolidayObservedDate = dtDate
    FROM @DateTable
    WHERE dtMonth = 'November'
    AND dtDayName = 'Thursday'
    AND dtDayRank = 4

Output

I liked how this solution worked out because I can identify any date in the year by using a predicate equal to the month, day of the week and how many times this day of the week has occurred in this month. I made this into a stored procedure and table valued function so that I can run it by passing a year and it returns all holidays for that year.

Is this a good solution...is there an easier way?