CODEWITHSUNDEEP
sqloracle-databasefunctionplsqlcalendar
Alessandro Tosato
Alessandro Tosato

Reputation: 3

Creating a SQL Function that return if a date it's an holiday

I need to create a calendar that display all the days of a month in a single row. Then I need to display another row for the same month but with the first letter of the day (M for monday - S for Sunday etc...) with a letter added if it's an holiday date (C for Civil and R for religious).

An example of output:

Date           Day
gennaio  2022, 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
gennaio  2022, Sr, D, L, M, M, Gr, V, S, D,  L,  M,  M,  G,  V,  S,  D,  L,  M,  M,  G,  V,  S,  D,  L,  M,  M,  G,  V,  S,  D,  L
febbraio 2022, 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, <null>, <null>, <null>
febbraio 2022, M,  M, G, V, S,  D, L, M, M,  G,  V,  S,  D,  L,  M,  M,  G,  V,  S,  D,  L,  M,  M,  G,  V,  S,  D,  L, <null>, <null>, <null>

I did it using a table with all the holiday dates and doing a select from it to return if that day was an holiday or not.

I would like to know if it's possible, and how, to create a function that return if date it's holiday or not without the needs of additional tables (that i created from 1-01-2021 to 31-12-2025, meaning that a earlier or later that would give an error)

I'm italian and since all the holidays are fixed except for easter, I suppose the function just need to calculate easter date and I can insert all the other holidays.

The idea it's to do a select from an interval of dates (in my case it's from 1-01-2021 to 31-12-2025) via the function and then formatting it to have the 2 columns as show above with all the dates and holidays.

Thank you all in advance.

Upvotes: 0

Views: 1314

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59436

You can calculate the Easter day according to Gauss's Easter algorithm:

CREATE OR REPLACE FUNCTION Easter(yyyy IN INTEGER DEFAULT TO_CHAR(SYSDATE, 'YYYY')) RETURN DATE IS

    a INTEGER;
    b INTEGER;
    c INTEGER;
    d INTEGER;
    e INTEGER;
    f INTEGER;
    g INTEGER;
    h INTEGER;
    i INTEGER;
    k INTEGER;
    l INTEGER;
    m INTEGER;
    n INTEGER;
    p INTEGER;
    EM INTEGER;
    ED INTEGER;

BEGIN

    a := yyyy MOD 19;
    b := TRUNC(yyyy / 100);
    c := yyyy MOD 100;
    d := TRUNC(b / 4);
    e := b MOD 4;
    f := TRUNC((b + 8) / 25);
    g := TRUNC((b - f + 1) / 3);
    h := (19*a + b - d - g + 15) MOD 30;
    i := TRUNC(c / 4);
    k := c MOD 4;
    l := (32 + 2*e + 2*i - h - k) MOD 7;
    m := TRUNC((a + 11*h + 22*l) / 451);
    n := TRUNC((h + l - 7*m + 114) / 31);
    p := (h + l - 7*m + 114) MOD 31;
    EM := n;
    ed := p + 1;
    
    RETURN TO_DATE(yyyy||'-'||EM||'-'||ed, 'YYYY-MM-DD');
END Easter;

For other days, you have to put some simple logic, e.g.

TO_CHAR(inputDate, 'MM-DD') = '12-25'

for Christmas day. Some holidays are defined like "the 3rd Monday in November", it could be this one:

TRUNC(inputDate) = NEXT_DAY(TRUNC(inputDate, 'MM')-1, 'MONDAY') + 2 * 7

Simplified example:

CREATE OR REPLACE FUNCTION IsHoliday(d IN DATE) RETURN BOOLEAN IS
   easter DATE := Easter(TO_CHAR(d, 'YYYY'));
BEGIN

   IF TO_CHAR(d, 'Dy', 'NLS_DATE_LANGUAGE = "American"') IN ('Sat','Sun') THEN RETURN TRUE; -- Weekend
   IF TRUNC(d) = easter + 1 THEN RETURN TRUE; -- Easter Monday
   IF TO_CHAR(d, 'MM-DD') IN ('12-25', '12-26') THEN RETURN TRUE; -- Christmas
   IF TO_CHAR(d, 'MM-DD') = '05-01' THEN RETURN TRUE; -- Festa del Lavoro
   ... some more
   RETURN FALSE;

END IsHoliday;

Another way of doing this, would be the DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING, for example like this:

DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING(
   --Wednesday before November 23th 
   'FREQ=DAILY;BYDATE=1122-SPAN:7D;BYDAY=WED', 
   NULL, TRUNC(d, 'YYYY')-1, next_run_date);
IF TRUNC(d) = next_run_date THEN RETURN TRUE;

See more examples

Upvotes: 1

Related Questions