oracle - Sql: difference between two dates

Question

one short question:

I am using this code

SELECT trunc (SYSDATE) - TO_DATE('04/10/2015','mm/dd/yyyy') Differenz FROM DUAL;

to calculate the differnece between two dates.

I would like to add that it ignores Saturdays, Sundays and holidays (not school holidays I mean holidays like christmas, eastern..). My local time zone is Germany (each country has its own holidays).

Thanks for help!

Answer 1

Getting the number of days exclude Saturday and Sunday is not so difficult, you will find several solutions for that at SO.

Considering holidays is more challenging. One solution is be to use the Oracle SCHEDULER. By default this is used for SCHEDULER JOBS, however I don't see any reason not using it for other purpose.

Biggest problem is the easter day, see here: Computus. I think the most efficient way is to hard-code the dates and maintaine them manually.

BEGIN
    DBMS_SCHEDULER.CREATE_SCHEDULE('New_Year', repeat_interval => 'FREQ=YEARLY;BYDATE=0101');

    DBMS_SCHEDULER.CREATE_SCHEDULE('Easter_Sunday',  repeat_interval => 'FREQ=YEARLY;BYDATE=20150405,    20160327,    20170416,    20170416,    20180401,    20190421,    20200412', comments => 'Hard coded till 2020');
    DBMS_SCHEDULER.CREATE_SCHEDULE('Good_Friday',    repeat_interval => 'FREQ=YEARLY;BYDATE=20150405-2D, 20160327-2D, 20170416-2D, 20170416-2D, 20180401-2D, 20190421-2D, 20200412-2D');
    DBMS_SCHEDULER.CREATE_SCHEDULE('Easter_Monday',   repeat_interval => 'FREQ=YEARLY;BYDATE=20150405+1D, 20160327+1D, 20170416+1D, 20170416+1D, 20180401+1D, 20190421+1D, 20200412+1D');
    DBMS_SCHEDULER.CREATE_SCHEDULE('Ascension_Day',   repeat_interval => 'FREQ=YEARLY;BYDATE=20150405+39D,20160327+39D,20170416+39D,20170416+39D,20180401+39D,20190421+39D,20200412+39D');
    DBMS_SCHEDULER.CREATE_SCHEDULE('Pentecost_Monday', repeat_interval => 'FREQ=YEARLY;BYDATE=20150405+50D,20160327+50D,20170416+50D,20170416+50D,20180401+50D,20190421+50D,20200412+50D');

    DBMS_SCHEDULER.CREATE_SCHEDULE('Repentance_and_Prayer', repeat_interval => 'FREQ=DAILY;BYDATE=1122-SPAN:7D;BYDAY=WED', 
        comments => 'Wednesday before November 23th, Buss- und Bettag');
    -- alternative solution: 
    --DBMS_SCHEDULER.CREATE_SCHEDULE('Repentance_and_Prayer', repeat_interval => 'FREQ=MONTHLY;BYMONTH=NOV;BYDAY=3 WED', 
    --    comments => '3rd Wednesday in November');

    DBMS_SCHEDULER.CREATE_SCHEDULE('Labor_Day', repeat_interval => 'FREQ=YEARLY;BYDATE=0501');
    DBMS_SCHEDULER.CREATE_SCHEDULE('German_Unity_Day', repeat_interval => 'FREQ=YEARLY;BYDATE=1003');
    DBMS_SCHEDULER.CREATE_SCHEDULE('Christmas', repeat_interval => 'FREQ=YEARLY;BYDATE=1225+SPAN:2D');

    DBMS_SCHEDULER.CREATE_SCHEDULE('Christian_Celebration_Days', repeat_interval => 'FREQ=DAILY;INTERSECT=Easter_Sunday,Good_Friday,Easter_Monday,Ascension_Day,Pentecost_Monday,Repentance_and_Prayer,Christmas');
    -- alternative solution: 
    -- DBMS_SCHEDULER.CREATE_SCHEDULE('Christian_Celebration_Days', repeat_interval => 'FREQ=Good_Friday;BYDAY=1 MON, 6 THU,8 MON');
    DBMS_SCHEDULER.CREATE_SCHEDULE('Political_Holidays', repeat_interval => 'FREQ=DAILY;INTERSECT=New_Year,Labor_Day,German_Unity_Day');


END;
/

See syntax for calendar here: Calendaring Syntax

Then you can use the schedules like this:

CREATE OR REPLACE FUNCTION DateDiff(end_date IN TIMESTAMP) RETURN INTEGER AS
    next_run_date TIMESTAMP := TRUNC(SYSTIMESTAMP);
    res INTEGER := 0;
BEGIN
    IF end_date > SYSTIMESTAMP THEN
        LOOP
            DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('FREQ=DAILY;INTERVAL=1;BYDAY=MON,TUE,WED,THU,FRI; EXCLUDE=Christian_Celebration_Days,Political_Holidays', NULL, next_run_date, next_run_date);
            EXIT WHEN next_run_date >= end_date;
            res := res + 1;
        END LOOP;
        RETURN res;
    ELSE
        RAISE VALUE_ERROR;
    END IF;     
END;

SELECT DateDiff(TO_DATE('04/10/2015','mm/dd/yyyy')) AS Differenz FROM DUAL;

Output next 20 holiays for testing:

DECLARE
    next_run_date TIMESTAMP;
BEGIN
    FOR i IN 1..20 LOOP
        DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('FREQ=DAILY;INTERSECT='Christian_Celebration_Days,Political_Holidays', NULL, next_run_date, next_run_date);
        DBMS_OUTPUT.PUT_LINE(next_run_date);
    END LOOP;
END;

Update

I even found a more compact version:

BEGIN
    -- Start with first celebration day (good Friday), all dependent celebration days have to be after this day for proper calculation of schedule
    DBMS_SCHEDULER.CREATE_SCHEDULE('GOOD_FRIDAY', repeat_interval => 'FREQ=YEARLY;BYDATE=20100402,20110422,20120406,20130329,20140418,20150403,20160325,20170414,20180330,20190419,20200410,20210402,20220410,20230407,20240329,20250418,20260403,20270326,20280414,20290330,20300419', comments => 'Hard coded 2010 to 2030');
    -- Easter Sunday can be skipped for list of holidays, otherwise 'FREQ=Good_Friday;BYDAY=1 SUN+SPAN:2D'
    DBMS_SCHEDULER.CREATE_SCHEDULE('EASTER_MONDAY', repeat_interval => 'FREQ=Good_Friday;BYDAY=1 MON', comments => '1st Monday after Good Friday'
    DBMS_SCHEDULER.CREATE_SCHEDULE('ASCENSION_DAY', repeat_interval => 'FREQ=Good_Friday;BYDAY=6 THU', comments => '6th Thursday after Good Friday (40 days after Easter)');
    -- Pentecost Sunday can be skipped for list of holidays, otherwise 'FREQ=Good_Friday;BYDAY=8 SUN+SPAN:2D'
    DBMS_SCHEDULER.CREATE_SCHEDULE('PENTECOST_MONDAY', repeat_interval => 'FREQ=Good_Friday;BYDAY=8 MON', comments => '8th Monday after Good Friday (50 days after Easter)');
    DBMS_SCHEDULER.CREATE_SCHEDULE('EASTER_RELATED_DAYS', repeat_interval => 'FREQ=Good_Friday;BYDAY=1 MON, 6 THU,8 MON'
END;