/* Mostly about Oracle APEX, ORDS, PL/SQL and a bit of Linux in between */
In this blog post I would like to show how to calculate public holidays in Germany within a period of time using a table function in the Oracle database.
The calculation of the dates for the public holidays is made on the basis of the date of Easter (Easter Sunday). I have taken the following PL/SQL code from a post written by Thomas Uhren on his blog Oracle SQL und PL/SQL: Rund um Oracle und die Verwendung von SQL und PL/SQL von Thomas Uhren and added and adapted dates to include further public holidays in the federal state of North Rhine-Westphalia in Germany.
create or replace function get_easter_day
(
pi_year in integer
)
return date
as
v_k integer;
v_m integer;
v_s integer;
v_a integer;
v_d integer;
v_r integer;
v_og integer;
v_sz integer;
v_oe integer;
v_os integer;
v_day integer;
v_month integer;
begin
v_k := floor(pi_year / 100);
v_m := 15 + floor((3 * v_k + 3) / 4) - floor((8 * v_k + 13) / 25);
v_s := 2 - floor((3 * v_k + 3) / 4);
v_a := mod(pi_year, 19);
v_d := mod((19 * v_a + v_m), 30);
v_r := floor(v_d / 29) + (floor(v_d / 28) - floor(v_d / 29)) * floor(v_a / 11);
v_og := 21 + v_d - v_r;
v_sz := 7 - mod((pi_year + floor(pi_year / 4) + v_s), 7);
v_oe := 7 - mod(v_og - v_sz, 7);
v_os := v_og + v_oe;
if (v_os ⟹ 31) then
v_day := v_os;
v_month := 3;
else
v_day := v_os - 31;
v_month := 4;
end if;
return to_date(v_day || '.' || v_month || '.' || pi_year, 'DD.MM.YYYY');
end get_easter_day;
/
Afterwards, the object types can be created for the table function:
create type holiday_t as object
(
holiday_date date,
holiday_name varchar2(30),
holiday_only_in varchar2(100),
holiday_description varchar2(100)
);
/
create type holiday_tab as table of holiday_t;
/
After that, the table function can be created:
create or replace function get_german_holidays
(
pi_year_start in integer,
pi_year_end in integer
)
return holiday_tab pipelined
as
v_easter_day date;
begin
for y in pi_year_start .. pi_year_end
loop
v_easter_day := get_easter_day(y);
pipe row (
holiday_t(
to_date('01.01.' || y, 'DD.MM.YYYY'),
'Neujahrstag',
'',
'Gesetzlicher Feiertag'));
pipe row (
holiday_t(
v_easter_day - interval '48' day,
'Rosenmontag',
'',
'Frei'));
pipe row (
holiday_t(
v_easter_day - interval '2' day,
'Karfreitag',
'',
'Gesetzlicher Feiertag'));
pipe row (
holiday_t(
v_easter_day + interval '1' day,
'Ostermontag',
'',
'Gesetzlicher Feiertag'));
pipe row (
holiday_t(
to_date('01.05.' || y, 'DD.MM.YYYY'),
'Tag der Arbeit',
'',
'Gesetzlicher Feiertag'));
pipe row (
holiday_t(
v_easter_day + interval '39' day,
'Christi Himmelfahrt',
'',
'Gesetzlicher Feiertag'));
pipe row (
holiday_t(
v_easter_day + interval '50' day,
'Pfingstmontag',
'',
'Gesetzlicher Feiertag'));
pipe row (
holiday_t(
v_easter_day + interval '60' day,
'Fronleichnam',
'BW, BY, HE, NW, RP',
'Feiertag in BW, BY, HE, NW, RP'));
pipe row (
holiday_t(
to_date('03.10.' || y, 'DD.MM.YYYY'),
'Tag der Deutschen Einheit',
'',
'Gesetzlicher Feiertag'));
pipe row (
holiday_t(
to_date('01.11.' || y, 'DD.MM.YYYY'),
'Allerheiligen',
'BW, BY, NW, RP und SL',
'Feiertag in BW, BY, NW, RP und SL'));
pipe row (
holiday_t(
to_date('24.12.' || y, 'DD.MM.YYYY'),
'Heiligabend',
'',
'Frei'));
pipe row (
holiday_t(
to_date('25.12.' || y, 'DD.MM.YYYY'),
'1. Weihnachtsfeiertag',
'',
'Gesetzlicher Feiertag'));
pipe row (
holiday_t(
to_date('26.12.' || y, 'DD.MM.YYYY'),
'2. Weihnachtsfeiertag',
'',
'Gesetzlicher Feiertag'));
pipe row (
holiday_t(
to_date('31.12.' || y, 'DD.MM.YYYY'),
'Silvester',
'',
'Frei'));
end loop;
end get_german_holidays;
/
This script is also used in my next blog post PL/SQL: Identify dates of weekdays within time period using NEXT_DAY() function. There I explain how to use PL/SQL to dynamically determine days of the week.
Related posts:
Ermittlung von Feiertagen per Table Function (ora-sql-plsql.blogspot.com)