Sql: difference between two dates

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;