A common task for scheduling is to find when certain dates might occur. The package below includes functions to return various holidays observed in the U.S. for various years. The dates can be returned individually by calling specific holiday functions and passing in the year. For example:
SQL> select holidays.independence_day(2023) from dual; HOLIDAYS.I ---------- 2023-07-04 SQL> select holidays.thanksgiving(2023) from dual; HOLIDAYS.T ---------- 2023-11-23
You can also query a list of federally observed holidays by passing a range of years and it will return all U.S. holiday observation dates for those years.
SQL> select * from holidays.holiday_list(2020,2022); HOLIDAY_NAME OBSERVED -------------------------------------------------- ---------- New Years Day 2020-01-01 Birthday of Martin Luther King, Jr. 2020-01-20 Washington's Birthday 2020-02-17 Memorial Day 2020-05-25 Independence Day 2020-07-04 Labor Day 2020-09-07 Columbus Day 2020-10-12 Veterans Day 2020-11-11 Thanksgiving 2020-11-26 Christmas 2020-12-25 New Years Day 2021-01-01 Birthday of Martin Luther King, Jr. 2021-01-18 Washington's Birthday 2021-02-15 Memorial Day 2021-05-31 Juneteenth National Independence Day 2021-06-19 Independence Day 2021-07-04 Labor Day 2021-09-06 Columbus Day 2021-10-11 Veterans Day 2021-11-11 Thanksgiving 2021-11-25 Christmas 2021-12-25 New Years Day 2022-01-01 Birthday of Martin Luther King, Jr. 2022-01-17 Washington's Birthday 2022-02-21 Memorial Day 2022-05-30 Juneteenth National Independence Day 2022-06-19 Independence Day 2022-07-04 Labor Day 2022-09-05 Columbus Day 2022-10-10 Veterans Day 2022-11-11 Thanksgiving 2022-11-24 Christmas 2022-12-25
The functions do take into account legal changes over time. For instance, Juneteenth wasn’t signed into law until 2021, thus it does not appear in the list of holidays for 2020 in the example above.
Changes in the date rules for individual holidays are also observed – for example Thanksgiving was originally observed on the last Thursday of November, then it was changed to the second-to-last Thursday, and then finally in 1942 changed to the current standard of the fourth Thursday in November.
Similarly, changes in name such as Armistice Day to Veteran’s Day and the change from Decoration Day to Memorial day are also observed in the list. When invoking the functions for specific holidays. If the holiday was not observed (either at all or by a different name) in a given year the function will return NULL.
SQL> select holidays.decoration_day(1966), holidays.memorial_day(1966) from dual;
HOLIDAYS.D HOLIDAYS.M
---------- ----------
1966-05-30
SQL> select holidays.decoration_day(1967), holidays.memorial_day(1967) from dual;
HOLIDAYS.D HOLIDAYS.M
---------- ----------
1967-05-30
I’ve also included functions for Easter and Good Friday in the package even though they aren’t federal holidays. Unlike other holidays which are either fixed or simple offsets within a month, Easter has a complicated algorithm for deriving the date, so I’ve implemented Ronald W. Mallen’s algorithm in pl/sql. Rather than convert that function to my own coding style, I tried to keep the code similar to the original so as to make it easier for readers to compare for correctness in my translation.
I did not include other unobserved, but common holidays such as Valentine’s Day, Mother’s Day, Father’s Day, or Halloween; but it should (hopefully) be easy to model those holidays after the methods seen in other holiday functions.
FUNCTION halloween(p_year IN NUMBER)
RETURN DATE
IS
-- Halloween October 31
BEGIN
RETURN TO_DATE(TO_CHAR(p_year, 'fm9999') || '-10-31', 'yyyy-mm-dd');
END halloween;
I’ve use variations on this package to generate business holidays rather than federal holidays in order to schedule events around down-time in the office.
Note the package uses an object type and collection to support the list function.
CREATE OR REPLACE TYPE holiday_type AS OBJECT
(
Holiday_Name VARCHAR2(50),
Observed DATE
);
CREATE OR REPLACE TYPE holiday_tab AS TABLE OF holiday_type;
CREATE OR REPLACE PACKAGE holidays
AS
-- Routines to determine when U.S. observed holidays will occur.
-- For example: Arbor Day is not factored in.
--
-- .///.
-- (0 o)
---------------0000--(_)--0000---------------
--
-- Sean D. Stuber
-- sean.stuber@gmail.com
--
-- oooO Oooo
--------------( )-----( )---------------
-- \ ( ) /
-- \_) (_/
-- History:
-- 2002-06-15 Sean D. Stuber Original
-- 2023-01-02 Sean D. Stuber Added Juneteenth as well as historical changes and support for NLS changes
-- Holiday_List
-- Returns a collection of Holiday_Type objects for all US federally observed
-- holidays for the given years and all years inbetween.
FUNCTION holiday_list(p_start_year IN NUMBER, p_end_year IN NUMBER)
RETURN holiday_tab
PIPELINED;
FUNCTION new_years_day(p_year IN NUMBER)
RETURN DATE;
FUNCTION martin_luther_king_birthday(p_year IN NUMBER)
RETURN DATE;
FUNCTION washington_birthday(p_year IN NUMBER)
RETURN DATE;
FUNCTION decoration_day(p_year IN NUMBER)
RETURN DATE;
FUNCTION memorial_day(p_year IN NUMBER)
RETURN DATE;
FUNCTION juneteenth(p_year IN NUMBER)
RETURN DATE;
FUNCTION independence_day(p_year IN NUMBER)
RETURN DATE;
FUNCTION labor_day(p_year IN NUMBER)
RETURN DATE;
FUNCTION columbus_day(p_year IN NUMBER)
RETURN DATE;
FUNCTION armistice_day(p_year IN NUMBER)
RETURN DATE;
FUNCTION veterans_day(p_year IN NUMBER)
RETURN DATE;
FUNCTION thanksgiving(p_year IN NUMBER)
RETURN DATE;
FUNCTION christmas(p_year IN NUMBER)
RETURN DATE;
-- Easter and Good Friday are not federally observed holidays
-- but many other organizations do.
-- Thus they are included here for those uses.
FUNCTION easter(p_year IN NUMBER)
RETURN DATE;
FUNCTION good_friday(p_year IN NUMBER)
RETURN DATE;
END;
/
CREATE OR REPLACE PACKAGE BODY holidays
AS
-- Routines to determine when U.S. observed holidays will occur.
-- For example: Arbor Day is not factored in.
--
-- .///.
-- (0 o)
---------------0000--(_)--0000---------------
--
-- Sean D. Stuber
-- sean.stuber@gmail.com
--
-- oooO Oooo
--------------( )-----( )---------------
-- \ ( ) /
-- \_) (_/
-- History:
-- 2002-06-15 Sean D. Stuber Original
-- 2023-01-02 Sean D. Stuber Added Juneteenth as well as historical changes and support for NLS changes
-- Dates with known days of the week
-- Using these allows for calculation of floating holidays
-- regardless of the session's NLS settings
c_sunday CONSTANT DATE := DATE '2020-11-01';
c_monday CONSTANT DATE := DATE '2020-11-02';
c_tuesday CONSTANT DATE := DATE '2020-11-03';
c_wednesday CONSTANT DATE := DATE '2020-11-04';
c_thursday CONSTANT DATE := DATE '2020-11-05';
c_friday CONSTANT DATE := DATE '2020-11-06';
c_saturday CONSTANT DATE := DATE '2020-11-07';
FUNCTION new_years_day(p_year IN NUMBER)
RETURN DATE
IS
--New Year's Day January 1
v_date DATE;
BEGIN
-- Federal holidays observed nationally started January 6, 1885
-- Thus New Year's Day wasn't eligible until 1886
IF p_year >= 1886
THEN
v_date :=
TO_DATE(TO_CHAR(p_year, 'fm9999') || '-01-01', 'yyyy-mm-dd');
END IF;
RETURN v_date;
END new_years_day;
FUNCTION martin_luther_king_birthday(p_year IN NUMBER)
RETURN DATE
IS
-- Martin Luther King's Birthday 3rd Monday of January
v_date DATE;
BEGIN
-- Martin Luther King's Birthday was signed into law as a holiday November 2, 1983
-- Thus first occurrence is in 1984, although not observed until 1986 and not in all 50 states until 2000.
-- Use NEXT_DAY starting the day before January 1 to find the first Monday
-- then add 14 days to that to get the third Monday
IF p_year > 1983
THEN
v_date :=
NEXT_DAY(
TO_DATE(TO_CHAR(p_year, 'fm9999') || '-01-01',
'yyyy-mm-dd')
- 1,
TO_CHAR(c_monday, 'Day'))
+ 14;
END IF;
RETURN v_date;
END martin_luther_king_birthday;
FUNCTION washington_birthday(p_year IN NUMBER)
RETURN DATE
IS
-- Washington's Birthday 3rd Monday of February
v_date DATE;
BEGIN
-- Washington's birthday was signed into law as a holiday January 31, 1879
-- Originally it was observed on his Gregorian calendar birthdate of February 22.
-- I specify Gregorian because he was born while the Julian calendar was still used,
-- which would have marked his birthday as February 11.
-- By the time the holiday was created, the Gregorian calendar had been adopted.
-- In 1968, the Uniform Monday Holiday Act changed the observation date to be
-- the 3rd Monday of February beginning in 1971.
v_date :=
CASE
WHEN p_year BETWEEN 1879 AND 1970
THEN
TO_DATE(TO_CHAR(p_year, 'fm9999') || '-02-22',
'yyyy-mm-dd')
WHEN p_year >= 1971
THEN
-- Use NEXT_DAY from Jan 31, to find first Monday in Februrary,
-- then add 14 days from that to find the third Monday
NEXT_DAY(
TO_DATE(TO_CHAR(p_year, 'fm9999') || '-01-31',
'yyyy-mm-dd'),
TO_CHAR(c_monday, 'Day'))
+ 14
END;
RETURN v_date;
END washington_birthday;
FUNCTION decoration_day(p_year IN NUMBER)
RETURN DATE
IS
v_date DATE;
BEGIN
-- Although it was a holiday for federal workers in the District of Columbia in 1888
-- and recognized through most of the US for decades after
-- Decoration Day wasn't declared a national holiday until 1938
-- The name was changed to Memorial Day in 1967
IF p_year BETWEEN 1938 AND 1966
THEN
v_date :=
TO_DATE(TO_CHAR(p_year, 'fm9999') || '-05-30', 'yyyy-mm-dd');
END IF;
RETURN v_date;
END decoration_day;
FUNCTION memorial_day(p_year IN NUMBER)
RETURN DATE
IS
-- Memorial Day Last Monday in May
v_date DATE;
BEGIN
-- Decoration Day was declared a national holiday starting in 1938
-- The name was changed to Memorial Day in 1967
-- The date was changed from May 30 to the last Monday in May starting in 1971
v_date :=
CASE
WHEN p_year BETWEEN 1967 AND 1970
THEN
TO_DATE(TO_CHAR(p_year, 'fm9999') || '-05-30',
'yyyy-mm-dd')
WHEN p_year > 1971
THEN
NEXT_DAY(TO_DATE(p_year || '-05-24', 'yyyy-mm-dd'),
TO_CHAR(c_monday, 'Day'))
END;
RETURN v_date;
END memorial_day;
FUNCTION juneteenth(p_year IN NUMBER)
RETURN DATE
IS
-- Juneteenth National Independence Day June 19
v_date DATE;
BEGIN
-- Signed into law June 17, 2021 just 2 days prior and immediately in effect.
IF p_year >= 2021
THEN
v_date :=
TO_DATE(TO_CHAR(p_year, 'fm9999') || '-06-19', 'yyyy-mm-dd');
END IF;
RETURN v_date;
END juneteenth;
FUNCTION independence_day(p_year IN NUMBER)
RETURN DATE
IS
-- Independence Day July 4
v_date DATE;
BEGIN
-- Federal holidays observed nationally started January 6, 1885
IF p_year >= 1885
THEN
v_date :=
TO_DATE(TO_CHAR(p_year, 'fm9999') || '-07-04', 'yyyy-mm-dd');
END IF;
RETURN v_date;
END independence_day;
FUNCTION labor_day(p_year IN NUMBER)
RETURN DATE
IS
-- Labor Day 1st Monday of September
v_date DATE;
BEGIN
IF p_year >= 1894
THEN
-- Use NEXT_DAY starting from August 31, to find the first Monday in September
v_date :=
NEXT_DAY(
TO_DATE(TO_CHAR(p_year, 'fm9999') || '-08-31',
'yyyy-mm-dd'),
TO_CHAR(c_monday, 'Day'));
END IF;
RETURN v_date;
END labor_day;
FUNCTION columbus_day(p_year IN NUMBER)
RETURN DATE
IS
-- Columbus Day 2nd Monday of October
v_date DATE;
BEGIN
-- Although many states observe it as "Indigenous Peoples' Day"
-- the federal holiday name has yet to be changed
IF p_year >= 1968
THEN
-- Use NEXT_DAY starting from September 30, to find the first Monday in October
v_date :=
NEXT_DAY(
TO_DATE(TO_CHAR(p_year, 'fm9999') || '-09-30',
'yyyy-mm-dd'),
TO_CHAR(c_monday, 'Day'))
+ 7;
END IF;
RETURN v_date;
END columbus_day;
FUNCTION armistice_day(p_year IN NUMBER)
RETURN DATE
IS
-- Armistice Day November 11
v_date DATE;
BEGIN
IF p_year BETWEEN 1938 AND 1953
THEN
v_date :=
TO_DATE(TO_CHAR(p_year, 'fm9999') || '-11-11', 'yyyy-mm-dd');
END IF;
RETURN v_date;
END armistice_day;
FUNCTION veterans_day(p_year IN NUMBER)
RETURN DATE
IS
-- Veterans' Day November 11
-- except 1971-1977 when it was 4th Monday in October
v_date DATE;
BEGIN
CASE
WHEN (p_year BETWEEN 1954 AND 1970) OR p_year >= 1978
THEN
v_date := TO_DATE(p_year || '-11-11', 'yyyy-mm-dd');
WHEN p_year BETWEEN 1971 AND 1977
THEN
-- Use NEXT_DAY starting from September 30, to find the first Monday in October
-- then add 21 days to find the fourth Monday
v_date :=
NEXT_DAY(
TO_DATE(TO_CHAR(p_year, 'fm9999') || '-09-30',
'yyyy-mm-dd'),
TO_CHAR(c_monday, 'Day'))
+ 21;
ELSE
NULL;
END CASE;
RETURN v_date;
END veterans_day;
FUNCTION thanksgiving(p_year IN NUMBER)
RETURN DATE
IS
-- Thanksgiving Day
-- 1885-1938 Last Thursday in November
--- 1939-1941 Second to last Thursday in November ("Franksgiving" by FDR proclamation)
-- 1942-present 4th Thursday in November
v_date DATE;
BEGIN
-- Federal holidays observed nationally started January 6, 1885
v_date :=
CASE
WHEN p_year >= 1942
THEN
-- Use NEXT_DAY starting from October 31, to find the first Monday in November
-- then add 21 days to find the fourth Monday
NEXT_DAY(
TO_DATE(TO_CHAR(p_year, 'fm9999') || '-10-31',
'yyyy-mm-dd'),
TO_CHAR(c_thursday, 'Day'))
+ 21
WHEN p_year = 1939
THEN
TO_DATE('1939-11-23', 'yyyy-mm-dd')
WHEN p_year = 1940
THEN
TO_DATE('1940-11-21', 'yyyy-mm-dd')
WHEN p_year = 1941
THEN
TO_DATE('1941-11-20', 'yyyy-mm-dd')
WHEN p_year >= 1885
THEN
-- Use NEXT_DAY starting from November 23, because that leaves the 24th-30th, the last week to search
NEXT_DAY(
TO_DATE(TO_CHAR(p_year, 'fm9999') || '-11-23',
'yyyy-mm-dd'),
TO_CHAR(c_thursday, 'Day'))
END;
RETURN v_date;
END thanksgiving;
FUNCTION christmas(p_year IN NUMBER)
RETURN DATE
IS
-- Christmas Day December 25
v_date DATE;
BEGIN
-- Federal holidays observed nationally started January 6, 1885
IF p_year >= 1885
THEN
v_date :=
TO_DATE(TO_CHAR(p_year, 'fm9999') || '-12-25', 'yyyy-mm-dd');
END IF;
RETURN v_date;
END christmas;
FUNCTION holiday_list(p_start_year IN NUMBER, p_end_year IN NUMBER)
RETURN holiday_tab
PIPELINED
IS
/*
U.S. Federal Holidays Date of observation
=====================================================================
New Year's Day January 1
Birthday of Martin Luther King, Jr. 3rd Monday of January
Washington's Birthday 3rd Monday of February
Memorial Day Last Monday in May
Juneteenth National Independence Day June 19
Independence Day July 4
Labor Day 1st Monday of September
Columbus Day 2nd Monday of October
Veterans' Day November 11
Thanksgiving 4th Thursday of November
Christmas December 25
Exceptions:
If the holiday falls on a Saturday then push it back to Friday
(note this could push New Years observation into the previous year.)
If the holiday falls on a Sunday then push it forward to Monday.
Christmas and Christmas Eve will always try to create 4 day weekends
and have their own special cases for moving the observation date.
*/
v_name VARCHAR2(25);
v_date DATE;
BEGIN
-- Federal holidays observed nationally started January 6, 1885
FOR v_year IN GREATEST(p_start_year, 1885) .. p_end_year
LOOP
v_date := new_years_day(v_year);
IF v_date IS NOT NULL
THEN
PIPE ROW (holiday_type('New Years Day', v_date));
END IF;
v_date := martin_luther_king_birthday(v_year);
IF v_date IS NOT NULL
THEN
PIPE ROW (holiday_type('Birthday of Martin Luther King, Jr.',
v_date));
END IF;
v_date := washington_birthday(v_year);
IF v_date IS NOT NULL
THEN
PIPE ROW (holiday_type('Washington''s Birthday', v_date));
END IF;
v_date := decoration_day(v_year);
IF v_date IS NOT NULL
THEN
PIPE ROW (holiday_type('Decoration Day', v_date));
END IF;
v_date := memorial_day(v_year);
IF v_date IS NOT NULL
THEN
PIPE ROW (holiday_type('Memorial Day', v_date));
END IF;
v_date := juneteenth(v_year);
IF v_date IS NOT NULL
THEN
PIPE ROW (holiday_type('Juneteenth National Independence Day',
v_date));
END IF;
v_date := independence_day(v_year);
IF v_date IS NOT NULL
THEN
PIPE ROW (holiday_type('Independence Day', v_date));
END IF;
v_date := labor_day(v_year);
IF v_date IS NOT NULL
THEN
PIPE ROW (holiday_type('Labor Day', v_date));
END IF;
v_date := columbus_day(v_year);
IF v_date IS NOT NULL
THEN
PIPE ROW (holiday_type('Columbus Day', v_date));
END IF;
v_date := armistice_day(v_year);
IF v_date IS NOT NULL
THEN
PIPE ROW (holiday_type('Armistice Day', v_date));
END IF;
v_date := veterans_day(v_year);
IF v_date IS NOT NULL
THEN
PIPE ROW (holiday_type('Veterans Day', v_date));
END IF;
v_date := thanksgiving(v_year);
IF v_date IS NOT NULL
THEN
PIPE ROW (holiday_type('Thanksgiving', v_date));
END IF;
v_date := christmas(v_year);
IF v_date IS NOT NULL
THEN
PIPE ROW (holiday_type('Christmas', v_date));
END IF;
END LOOP;
RETURN;
END holiday_list;
/*
When will Easter fall for a given year?
Usually this will be used simply to find Good Friday since Easter
isn't an observed holiday because it already falls on a weekend.
Definition of Easter Sunday Date
from http://users.chariot.net.au/~gmarts/easter.htm
Easter Sunday is the Sunday following the Paschal Full Moon (PFM) date for the year.
In June 325 A.D. astronomers approximated astronomical full moon dates for the
Christian church, calling them Ecclesiastical Full Moon (EFM) dates.
From 326 A.D. the PFM date has always been the EFM date after March 20
(which was the equinox date in 325 A.D.).
This will find the Gregorian Easter Sunday (Western church) date for years 1583 to 4099
Algorithm originally written by Ronald W. Mallen, 1995
http://www.assa.org.au/edm.html
Modified for PL/SQL by Sean D. Stuber, 2003
*/
FUNCTION easter(p_year IN NUMBER)
RETURN DATE
IS
firstdig INTEGER; -- intermediate results
remain19 INTEGER; -- intermediate results
temp INTEGER; --intermediate results
ta INTEGER; -- "Tabular" values based on Paschal Full Moon
tb INTEGER;
tc INTEGER;
td INTEGER;
te INTEGER;
m INTEGER;
d INTEGER;
BEGIN
firstdig := TRUNC(p_year / 100); -- first 2 digits of year
remain19 := p_year MOD 19; -- remainder of year / 19
-- calculate PFM date
temp := TRUNC((firstdig - 15) / 2) + 202 - 11 * remain19;
IF firstdig IN (21, 24, 25, 27, 28, 29, 30, 31, 32, 34, 35, 38)
THEN
temp := temp - 1;
ELSIF firstdig IN (33, 36, 37, 39, 40)
THEN
temp := temp - 2;
END IF;
temp := temp MOD 30;
ta := temp + 21;
IF temp = 29
THEN
ta := ta - 1;
END IF;
IF (temp = 28 AND remain19 > 10)
THEN
ta := ta - 1;
END IF;
-- find the next Sunday
tb := (ta - 19) MOD 7;
tc := (40 - firstdig) MOD 4;
IF tc = 3
THEN
tc := tc + 1;
END IF;
IF tc > 1
THEN
tc := tc + 1;
END IF;
temp := p_year MOD 100;
td := (temp + TRUNC(temp / 4)) MOD 7;
te := ((20 - tb - tc - td) MOD 7) + 1;
d := ta + te;
--return the date
IF d > 31
THEN
d := d - 31;
m := 4;
ELSE
m := 3;
END IF;
RETURN TO_DATE(
p_year || '-' || TO_CHAR(m, '00') || '-' || TO_CHAR(d, '00'),
'yyyy-mm-dd');
END easter;
FUNCTION good_friday(p_year IN NUMBER)
RETURN DATE
IS
BEGIN
-- Easter is described as being the "third" day from Good Friday
-- but that's because Good Friday is included in the counted days.
-- Good Friday is Day 1, Saturday is Day 2, Easter Sunday is Day 3
-- But, mathematically, you return 2 days prior to Easter, not 3.
RETURN easter(p_year) - 2;
END good_friday;
END holidays;
I hope you find the package as useful as I have. Questions and comments, as always, are welcome.