/* 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
create or replace function 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 easter_day; |
Afterwards, the object types are created for the table function:
|
1 2 3 4 5 6 7 8 9 10 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 |
create or replace function 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 := 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 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.