[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 457

# Comverting Crystal repository Holiday function to pl/sql

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)
``````

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.
0
1 Solution

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

``````CREATE TABLE HOLIDAYS
(
DT DATE PRIMARY KEY
) ORGANIZATION INDEX;

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;
begin
select 1 into retval from holidays where dt = trunc(indate);
return 1;
exception when no_data_found then return 0;
end;
/
``````
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.
0

Author Commented:
PERFECT!!! Simple and not overly complicated, thanks a lot.
0

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.