February 7, 2012 at 12:14 pm
Thought this might be helpful. I've written a T-SQL function that returns a federal holiday name based on an input date. No external data sources are required. If the date is not one of the 10 designated federal holidays, the function returns NULL. I've tested the results against 5 previous years and it appears to be spot on.
New Year's Day
Martin Luther King Day
President's Day
Memorial Day
Independence Day
Labor Day
Columbus Day
Veteran's Day
Thanksgiving Day
Christmas Day
Here is the T-SQL to drop and create the function.
/****** Object: UserDefinedFunction [dbo].[IsFederalHoliday] Script Date: 02/07/2012 13:05:02 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[IsFederalHoliday]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[IsFederalHoliday]
GO
/****** Object: UserDefinedFunction [dbo].[IsFederalHoliday] Script Date: 02/07/2012 13:05:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[IsFederalHoliday] (@InputDate DATETIME)
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE@m TINYINT
DECLARE@D TINYINT
DECLARE@DW TINYINT
DECLARE @Holiday VARCHAR(50)
SET @Holiday = NULL
-- Day of Week (@DW)
-- 1 - Sunday
-- 2 - Monday
-- 3 - Tuesday
-- 4 - Wednesday
-- 5 - Thursday
-- 6 - Friday
-- 7 - Saturday
SET @m = DATEPART(MM, @InputDate)
SET@D = DATEPART(DD, @InputDate)
SET@DW = DATEPART(DW, @InputDate)
-- New Years Day
-- Falls on Weekday
IF (@M = 1 AND @D = 1 AND @DW BETWEEN 2 AND 6) SET @Holiday = 'New Year''s Day'
ELSE
-- Falls on Sunday
IF (@M = 1 AND @D = 2 AND @DW = 2) SET @Holiday = 'New Year''s Day'
ELSE
-- Falls on Saturday
IF (@M = 12 AND @D = 31 AND @DW = 6) SET @Holiday = 'New Year''s Day'
-- Martin Luther King Day (3rd Monday of January)
IF (@M = 1 AND @D BETWEEN 15 AND 21 AND @DW = 2) SET @Holiday = 'Martin Luther King Day'
-- Presidents Day (3rd Monday of February)
IF (@M = 2 AND @D BETWEEN 15 AND 21 AND @DW = 2) SET @Holiday = 'President''s Day'
-- Memorial Day (Last Monday of May)
IF (@M = 5 AND @D BETWEEN 25 AND 31 AND @DW = 2) SET @Holiday = 'Memorial Day'
-- Independence Day
-- Falls on Weekday
IF (@M = 7 AND @D = 4 AND @DW BETWEEN 2 AND 6) SET @Holiday = 'Independence Day'
ELSE
-- Falls on Sunday
IF (@M = 7 AND @D = 5 AND @DW = 2) SET @Holiday = 'Independence Day'
ELSE
-- Falls on Saturday
IF (@M = 7 AND @D = 3 AND @DW = 6) SET @Holiday = 'Independence Day'
-- Labor Day (1st Monday of September)
IF (@M = 9 AND @D BETWEEN 1 AND 7 AND @DW = 2) SET @Holiday = 'Labor Day'
-- Columbus Day (2nd Monday of October)
IF (@M = 10 AND @D BETWEEN 8 AND 14 AND @DW = 2) SET @Holiday = 'Columbus Day'
-- Veterans Day (October 11th)
-- Falls on Weekday
IF (@M = 11 AND @D = 11 AND @DW BETWEEN 2 AND 6) SET @Holiday = 'Veteran''s Day'
ELSE
-- Falls on Sunday
IF (@M = 11 AND @D = 12 AND @DW = 2) SET @Holiday = 'Veteran''s Day'
ELSE
-- Falls on Saturday
IF (@M = 11 AND @D = 10 AND @DW = 6) SET @Holiday = 'Veteran''s Day'
-- Thanksgiving Day (4th Thursday of November)
IF (@M = 11 AND @D BETWEEN 22 AND 28 AND @DW = 5) SET @Holiday = 'Thanksgiving Day'
-- Christmas Day (December 25th)
IF (@M = 12 AND @D = 25 AND @DW BETWEEN 2 AND 6) SET @Holiday = 'Christmas Day'
ELSE
-- Falls on Sunday
IF (@M = 12 AND @D = 26 AND @DW = 2) SET @Holiday = 'Christmas Day'
ELSE
-- Falls on Saturday
IF (@M = 12 AND @D = 24 AND @DW = 6) SET @Holiday = 'Christmas Day'
RETURN @Holiday
END
GO
May 2, 2017 at 7:51 am
Useful post - thanks!
May 2, 2017 at 9:21 am
dbunch 89261 - Tuesday, May 2, 2017 7:51 AMUseful post - thanks!
You'd probably find an inline function significantly more performant than this UDF: somethink like this:DECLARE @MyDate DATETIME = GETDATE()
SELECT Holiday = CASE
WHEN [Month] = 1 AND [DayOfMonth] = 1 THEN 'New Year'
WHEN [Month] = 5 AND [DayOfMonth] >= 25 AND [DayName] = 'Monday' THEN 'Memorial Day'
WHEN [Month] = 7 AND [DayOfMonth] = 4 THEN 'Independence Day'
WHEN [Month] = 9 AND [DayOfMonth] <= 7 AND [DayName] = 'Monday' THEN 'Labor Day'
WHEN [Month] = 11 AND [DayOfMonth] BETWEEN 22 AND 28 AND [DayName] = 'Thursday' THEN 'Thanksgiving Day'
WHEN [Month] = 12 AND [DayOfMonth] = 25 THEN 'Christmas Day'
ELSE NULL END
FROM (
SELECT
[Month] = MONTH(@MyDate),
[DayOfMonth] = DAY(@MyDate),
[DayName] = DATENAME(weekday,@MyDate)
) c
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 2, 2017 at 11:19 am
Chris, while your query might perform a bit better, one thing it doesn't do that JHaddens does is adjust for when the holiday in question falls on a weekday.
Basically, what his query returns is the day that a Federal Employee will have off, which changes if the holiday in question (Independence Day, New Years / Christmas, Veteran's Day) falls on a weekend.
May 2, 2017 at 12:48 pm
You may need to implement a Localization ID. Depending on the US state, "Martin Luther King Day" may be referred to as "Robert E. Lee's Birthday", and "Columbus Day" is referred to as "Indigenous People's Day", to say the least. There are many other variations on which states officially observe which federal holidays.
https://en.wikipedia.org/wiki/Public_holidays_in_the_United_States
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 2, 2017 at 12:58 pm
For a case like this, nothing beats a good ol' fashioned calendar table... At least in my humble opinion...
Fact is, holidays tend not to change very often (if ever) there is absolutely no good reason to calculate them on the fly. Simply mark your holidays on the Calendar table one time, index as necessary, and you're good to go.
May 2, 2017 at 1:18 pm
Here's another alternative. Btw, I corrected two holiday descriptions, for Jan and Feb. It's not "President's Day", it's "[George] Washington's Birthday"; I wish the media would stop repeating the wrong name ("fake news"? 🙂 )
I used a CTE for the dates so that it's easier to feed in multiple dates to fully test the logic, which I have not done, although I did test for Christmas last year.
DECLARE @InputDate datetime
SET @InputDate = '20161226'
;WITH CTEInputDates AS (
SELECT @InputDate AS InputDate
),
CTEInputValues AS (
SELECT
InputDate,
MONTH(InputDate) AS InputMonth,
DAY(InputDate) AS InputDay,
CAST(DATEDIFF(DAY, 0, InputDate) % 7 AS tinyint) AS InputDayOfWeek,
0 AS Monday, 3 AS Thursday, 4 AS Friday, 5 AS Saturday, 6 AS Sunday
FROM CTEInputDates
)
SELECT
CASE /* Fixed-WeekAndDayOfWeek Holidays*/
WHEN InputMonth = 1 AND InputDayOfWeek = Monday AND InputDay BETWEEN 15 AND 21
THEN 'Martin Luther King, Jr. Day'
WHEN InputMonth = 2 AND InputDayOfWeek = Monday AND InputDay BETWEEN 15 AND 21
THEN 'Washington''s Birthday'
WHEN InputMonth = 5 AND InputDayOfWeek = Monday AND InputDay >= 22 AND MONTH(DATEADD(DAY, 7, InputDate)) = 6
THEN 'Memorial Day'
WHEN InputMonth = 9 AND InputDayOfWeek = Monday AND InputDay BETWEEN 1 AND 7
THEN 'Labor Day'
WHEN InputMonth = 10 AND InputDayOfWeek = Monday AND InputDay BETWEEN 8 AND 14
THEN 'Columbus Day'
WHEN InputMonth = 11 AND InputDayOfWeek = Thursday AND InputDay BETWEEN 22 AND 28
THEN 'Thanksgiving Day'
/* Fixed-Date Holidays */
WHEN (InputMonth = 1 AND InputDayOfWeek < Saturday AND InputDay = 1)
OR (InputMonth = 12 AND InputDayOfWeek = Friday AND InputDay = 31)
OR (InputMonth = 1 AND InputDayOfWeek = Monday AND InputDay = 2)
THEN 'New Year''s Day'
WHEN (InputMonth = 7 AND InputDayOfWeek < Saturday AND InputDay = 4)
OR (InputMonth = 7 AND InputDayOfWeek = Friday AND InputDay = 3)
OR (InputMonth = 7 AND InputDayOfWeek = Monday AND InputDay = 5)
THEN 'Independence Day'
WHEN (InputMonth = 10 AND InputDayOfWeek < Saturday AND InputDay = 11)
OR (InputMonth = 10 AND InputDayOfWeek = Friday AND InputDay = 10)
OR (InputMonth = 10 AND InputDayOfWeek = Monday AND InputDay = 12)
THEN 'Veterans Day'
WHEN (InputMonth = 12 AND InputDayOfWeek < Saturday AND InputDay = 25)
OR (InputMonth = 12 AND InputDayOfWeek = Friday AND InputDay = 24)
OR (InputMonth = 12 AND InputDayOfWeek = Monday AND InputDay = 26)
THEN 'Christmas Day'
ELSE NULL END
FROM CTEInputValues
Interesting: it seems to have cut my indentations from 4 spaces to 2, I'm not sure why, but I'm not gonna worry about trying to fix just that.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 2, 2017 at 1:52 pm
ScottPletcher - Tuesday, May 2, 2017 1:18 PMHere's another alternative. Btw, I corrected two holiday descriptions, for Jan and Feb. It's not "President's Day", it's "[George] Washington's Birthday"; I wish the media would stop repeating the wrong name ("fake news"? 🙂 )I used a CTE for the dates so that it's easier to feed in multiple dates to fully test the logic, which I have not done, although I did test for Christmas last year.
DECLARE @InputDate datetime
SET @InputDate = '20161226';WITH CTEInputDates AS (
SELECT @InputDate AS InputDate
),
CTEInputValues AS (
SELECT
InputDate,
MONTH(InputDate) AS InputMonth,
DAY(InputDate) AS InputDay,
CAST(DATEDIFF(DAY, 0, InputDate) % 7 AS tinyint) AS InputDayOfWeek,
0 AS Monday, 3 AS Thursday, 4 AS Friday, 5 AS Saturday, 6 AS Sunday
FROM CTEInputDates
)
SELECT
CASE /* Fixed-WeekAndDayOfWeek Holidays*/
WHEN InputMonth = 1 AND InputDayOfWeek = Monday AND InputDay BETWEEN 15 AND 21
THEN 'Martin Luther King, Jr. Day'
WHEN InputMonth = 2 AND InputDayOfWeek = Monday AND InputDay BETWEEN 15 AND 21
THEN 'Washington''s Birthday'
WHEN InputMonth = 5 AND InputDayOfWeek = Monday AND InputDay >= 22 AND MONTH(DATEADD(DAY, 7, InputDate)) = 6
THEN 'Memorial Day'
WHEN InputMonth = 9 AND InputDayOfWeek = Monday AND InputDay BETWEEN 1 AND 7
THEN 'Labor Day'
WHEN InputMonth = 10 AND InputDayOfWeek = Monday AND InputDay BETWEEN 8 AND 14
THEN 'Columbus Day'
WHEN InputMonth = 11 AND InputDayOfWeek = Thursday AND InputDay BETWEEN 22 AND 28
THEN 'Thanksgiving Day'
/* Fixed-Date Holidays */
WHEN (InputMonth = 1 AND InputDayOfWeek < Saturday AND InputDay = 1)
OR (InputMonth = 12 AND InputDayOfWeek = Friday AND InputDay = 31)
OR (InputMonth = 1 AND InputDayOfWeek = Monday AND InputDay = 2)
THEN 'New Year''s Day'
WHEN (InputMonth = 7 AND InputDayOfWeek < Saturday AND InputDay = 4)
OR (InputMonth = 7 AND InputDayOfWeek = Friday AND InputDay = 3)
OR (InputMonth = 7 AND InputDayOfWeek = Monday AND InputDay = 5)
THEN 'Independence Day'
WHEN (InputMonth = 10 AND InputDayOfWeek < Saturday AND InputDay = 11)
OR (InputMonth = 10 AND InputDayOfWeek = Friday AND InputDay = 10)
OR (InputMonth = 10 AND InputDayOfWeek = Monday AND InputDay = 12)
THEN 'Veterans Day'
WHEN (InputMonth = 12 AND InputDayOfWeek < Saturday AND InputDay = 25)
OR (InputMonth = 12 AND InputDayOfWeek = Friday AND InputDay = 24)
OR (InputMonth = 12 AND InputDayOfWeek = Monday AND InputDay = 26)
THEN 'Christmas Day'
ELSE NULL END
FROM CTEInputValuesInteresting: it seems to have cut my indentations from 4 spaces to 2, I'm not sure why, but I'm not gonna worry about trying to fix just that.
Bwahaaaa! This is almost identical to the function I cut the above code from!
I only posted a piece because the rest is trivially easy to figure out. Fishing rod plus bait...
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
May 2, 2017 at 4:19 pm
Eh... What the heck...
CREATE FUNCTION dbo.tfn_FederalHoliday
/* =====================================================================================================
05/02/2017 JL, Created. If the supplied is a federal holiday, the function will return the holiday name.
===================================================================================================== */
(
@Date DATE
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
WITH
cte_Holiday AS (
SELECT
h.HolidayName,
h.MM,
h.DD,
h.DW,
h.BOR,
h.EOR
FROM ( VALUES
('New Year''s Day', 1, 1, NULL, NULL, NULL),
('Martin Luther King Day', 1, NULL, 2, 15, 21),
('Washington''s Birthday', 2, NULL, 2, 15, 21),
('Memorial Day', 5, NULL, 2, 25, 31),
('Independence Day', 7, 4, NULL, NULL, NULL),
('Labor Day', 9, NULL, 2, 1, 7),
('Columbus Day', 10, NULL, 2, 8, 14),
('Veterans Day', 11, 11, NULL, NULL, NULL),
('Thanksgiving Day', 11, NULL, 5, 22, 28),
('Christmas Day', 12, 25, NULL, NULL, NULL)
) h (HolidayName, MM, DD, DW, BOR, EOR)
),
cte_DateParts AS (
SELECT
dp.MM,
dp.DD,
dp.DW,
dd.DDfix,
Observed = CASE WHEN dp.DD = dd.DDfix THEN '' ELSE ' (observed)' END
FROM
( VALUES (MONTH(@Date), DAY(@Date), DATEPART(dw, @Date)) ) dp (MM, DD, DW)
CROSS APPLY ( VALUES (CASE dp.DW
WHEN 2 THEN DAY(DATEADD(dd, -1, @Date))
WHEN 6 THEN DAY(DATEADD(dd, 1, @Date))
ELSE dp.DD
END)
) dd (DDfix)
)
SELECT
HolidayName = CONCAT(h.HolidayName, CASE WHEN h.DD = dp.DD THEN '' ELSE dp.Observed END)
FROM
cte_Holiday h
JOIN cte_DateParts dp
ON h.MM = dp.MM
AND (
h.DD = dp.DD
OR
h.DD = dp.DDfix
OR
(
dp.DD BETWEEN h.BOR AND h.EOR
AND h.DW = dp.DW
)
);
GO
May 17, 2018 at 8:33 am
I required a table of US holidays so adapted ChrisM's solution (above) as follows to list 40 years of US holidays.....
DECLARE @start_date date = 'Jan 1, 2010';
DECLARE @end_date date = dateadd(year,40,@start_date);
WITH AllDays
AS (
SELECT @start_date AS [Date], month( @start_date) as mnth, Day( @start_date) as dayofM, DateName(weekday, @start_date) as dtname
UNION ALL
SELECT
DATEADD(DAY, 1, [Date]), month( DATEADD(DAY, 1, [Date])) as mnth, Day( DATEADD(DAY, 1, [Date])) as dayofM, DateName(weekday,DATEADD(DAY, 1, [Date])) as dtname
FROM AllDays
WHERE [Date] < @end_date
)
SELECT [Date], mnth, dayofM, dtname
FROM AllDays
where
(
(mnth =1 and dayofM = 1) /* new years */
or (mnth =5 and dayofM >= 25 and dtname = 'Monday') /* memorial day */
or (mnth =7 and dayofM = 4) /* Indendence day */
or (mnth =9 and dayofM <= 7 and dtname = 'Monday') /* Labour day */
or (mnth =11 and dayofM between 22 and 28 and dtname = 'Thursday') /* Thanksgiving */
or (mnth =12 and dayofM = 25) /* Xmas */
)
OPTION (MAXRECURSION 0)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply