CODEWITHSUNDEEP
oracle-database
dotnetspark
dotnetspark

Reputation: 581

How to get USA Holidays with Oracle query?

I was recently asked to come up with an Oracle query that returns the USA holidays. After doing some research I couldn't find anything in this regard. Most of the solutions found were for C# or VB.

Upvotes: 0

Views: 1511

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59436

You can use the the build-in SCHEDULER SCHEDULE from Oracle. First you have to create the holiday schedules, here are some examples:

BEGIN

    DBMS_SCHEDULER.CREATE_SCHEDULE('NEW_YEARS_DAY', repeat_interval => 'FREQ=YEARLY;INTERVAL=1;BYDATE=0101');
    DBMS_SCHEDULER.CREATE_SCHEDULE('MARTIN_LUTHER_KING_DAY', repeat_interval => 'FREQ=MONTHLY;BYMONTH=JAN;BYDAY=3 MON', comments => 'Third Monday of January');
    DBMS_SCHEDULER.CREATE_SCHEDULE('WASHINGTONS_BIRTHDAY', repeat_interval => 'FREQ=MONTHLY;BYMONTH=FEB;BYDAY=3 MON', comments => 'Third Monday of February');
    DBMS_SCHEDULER.CREATE_SCHEDULE('MEMORIAL_DAY', repeat_interval => 'FREQ=MONTHLY;BYMONTH=MAY;BYDAY=-1 MON', comments => 'Last Monday of May');
    DBMS_SCHEDULER.CREATE_SCHEDULE('INDEPENDENCE_DAY', repeat_interval => 'FREQ=YEARLY;INTERVAL=1;BYDATE=0704');
    DBMS_SCHEDULER.CREATE_SCHEDULE('CHRISTMAS_DAY', repeat_interval => 'FREQ=YEARLY;INTERVAL=1;BYDATE=1225');
    DBMS_SCHEDULER.CREATE_SCHEDULE('SPRING_BREAK', repeat_interval => 'FREQ=YEARLY;BYDATE=0301+SPAN:7D');

    DBMS_SCHEDULER.CREATE_SCHEDULE('ALL_HOLIDAYS', repeat_interval => 'FREQ=DAILY;INTERSECT=CHRISTMAS_DAY,INDEPENDENCE_DAY,MARTIN_LUTHER_KING_DAY,MEMORIAL_DAY,NEW_YEARS_DAY,SPRING_BREAK,WASHINGTONS_BIRTHDAY');

END;

See syntax for calendar here: Calendaring Syntax

Then you can interrogate them with DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING

Example: Next Memorial day

DECLARE
    start_time TIMESTAMP := TRUNC(LOCALTIMESTAMP);
    next_run_date TIMESTAMP := start_time;
BEGIN
    DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('MEMORIAL_DAY', NULL, next_run_date, next_run_date);
    DBMS_OUTPUT.PUT_LINE ( next_run_date );
END;

Example: All holidays for this year

DECLARE
    start_time TIMESTAMP := TIMESTAMP '2016-12-31 00:00:00';
    next_run_date TIMESTAMP := start_time;
BEGIN
    LOOP
        DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('ALL_HOLIDAYS', NULL, next_run_date, next_run_date);
        EXIT WHEN next_run_date > TIMESTAMP '2017-12-31 00:00:00';
        DBMS_OUTPUT.PUT_LINE ( next_run_date );
    END LOOP;
END;

Example: Next 10 Washington's birthdays

DECLARE
    start_time TIMESTAMP := TRUNC(LOCALTIMESTAMP);
    next_run_date TIMESTAMP := start_time;
BEGIN
    FOR i IN 1..10 LOOP
        DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('WASHINGTONS_BIRTHDAY', NULL, next_run_date, next_run_date);
        DBMS_OUTPUT.PUT_LINE ( next_run_date );
    END LOOP;
END;

Upvotes: 2

dotnetspark
dotnetspark

Reputation: 581

Below I'm sharing the query. Please bear in mind that in some states you may have more holidays but I just wanted to keep it simply by just listing the most relevant. 

/*
    New Year's Day -------------- January 1st
    Martin Luther King, Jr. ----- Third Monday in January
    Washington's Birthday ------- Third Monday in February
    Memorial Day ---------------- Last Monday in May
    Independence Day ------------ July 4th
    Labor Day ------------------- First Monday in September
    Columbus Day ---------------- Second Monday in October
    Veterans Day ---------------- November 11
    Thanksgiving Day ------------ Fourth Thursday in November
    Christmas Day --------------- December 25th
*/

SELECT NEXT_DAY(TRUNC(SYSDATE, 'YYYY') - 1, 'MONDAY') FROM DUAL UNION
SELECT NEXT_DAY(TRUNC(SYSDATE, 'YYYY') - 1, 'MONDAY') + 14 FROM DUAL UNION
SELECT NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 1) - 1, 'MONDAY') + 14 FROM DUAL UNION
SELECT NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 5) - 1, 'MONDAY') - 7 FROM DUAL UNION
SELECT ADD_MONTHS(TRUNC(SYSDATE,'YYYY'),6) + 3 FROM DUAL UNION
SELECT NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE,'YYYY'),8), 'MONDAY') FROM DUAL UNION
SELECT NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE,'YYYY'),9) - 1, 'MONDAY') + 7 FROM DUAL UNION
SELECT ADD_MONTHS(TRUNC(SYSDATE,'YYYY'),10) + 10 FROM DUAL UNION
SELECT NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 10) - 1, 'THURSDAY') + 21 FROM DUAL UNION
SELECT ADD_MONTHS(TRUNC(SYSDATE,'YYYY'),11) + 24 FROM DUAL;

Upvotes: 2

Related Questions