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

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

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)

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.
0
dprasad
Asked:
dprasad
1 Solution
 
mrjoltcolaCommented:
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;
/

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.
0
 
dprasadAuthor Commented:
PERFECT!!! Simple and not overly complicated, thanks a lot.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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