Comverting Crystal repository Holiday function to pl/sql

Posted on 2011-05-10
Last Modified: 2012-05-11
Hello. We currently have a Crystal repository function used to check for holidays:

Function (DateVar D_1)

//This function compares D_1 to an array of Holidays.  The function returns TRUE if the date falls on a
//holiday, or FALSE otherwise.  Holidays are updated through New Years Day 2011.

// Sodexo - NA

Local DateVar Array Holidays := [Date(2010,01,01), Date(2010,05,31), Date(2010,07,05), Date(2010,09,06),
                                 Date(2010,11,25), Date(2010,11,26), Date(2010,12,24)];

(If D_1 in Holidays Then TRUE Else FALSE)

Open in new window

Can someone point me to a good example of a similar such function for Oracle? And it doesn't necessarily have to be a function, just whatever works.

Typically, if a date range fals on a holiday, we back that holiday date out of the query. Am not able to find good examples from google.
Question by:dprasad
    LVL 40

    Accepted Solution

    How about a HOLIDAYS table, and a PL/SQL function...

    insert into holidays values(to_date('DD-MON-YYYY', '25-DEC-2011'));
    insert into holidays values(to_date('DD-MON-YYYY', '01-JAN-2012'));
    insert into holidays values(to_date('DD-MON-YYYY', '25-DEC-2012'));
    CREATE OR REPLACE function isholiday(indate in date)
    return int as
       retval int;
       select 1 into retval from holidays where dt = trunc(indate);
       return 1;
       exception when no_data_found then return 0;

    Open in new window

    Just make sure to populate the holidays table with truncated dates (using the TO_DATE() or TRUNC() function).

      select isholiday(mydate) from dual;

    And add it to a where clauses:

      AND isholiday(mydate) = 0

    You could also skip the function and join directly against the HOLIDAYS table in your queries.

    Author Closing Comment

    PERFECT!!! Simple and not overly complicated, thanks a lot.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example, show how to take different types of Oracle backups using RMAN.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now