CODEWITHSUNDEEP
sqloracle-databasedate
Zolt
Zolt

Reputation: 2811

Determine holidays in Oracle

Is there a default function in Oracle that allows you to pass the date as the parameter, and it returns whether it is a US holiday or not?

something like

IS_HOLIDAY(:DATEINPUT)

I need to do this without a stored procedure. In my select statement, what I'd like to do is filter out data based on holday, in my where clause.

Please help.

Upvotes: 0

Views: 11905

Answers (3)

Sun
Sun

Reputation: 2715

Couldn't you store the US holidays in a different table, then join it?

I'm sure there is free public data out there that will provide what you need. For example, this site displays all US bank holidays from 2012 to 2020: https://gist.github.com/shivaas/4758439

Upvotes: 0

user2225399
user2225399

Reputation: 69

Here is a function I wrote to test if us holiday. Note if you need first business day after a govt holiday or other holiday then you can use second function as well.

create or replace 
FUNCTION IS_HOLIDAY(V_DT DATE) RETURN VARCHAR2 AS
RET VARCHAR2(1);
V_MONTH VARCHAR2(2);
V_DAY VARCHAR2(2);
V_YEAR VARCHAR2(4); 
MLK DATE;
WASHINGTON DATE;
MEMORIAL DATE;
COLUMBUS DATE;
THANKSGIVING DATE;
BEGIN
 RET:='F';
 SELECT TO_CHAR(V_DT,'YYYY') INTO V_YEAR FROM DUAL;
 SELECT TO_CHAR(V_DT,'MM') INTO V_MONTH FROM DUAL;
 SELECT TO_CHAR(V_DT,'dd') INTO V_DAY FROM DUAL;
 SELECT NEXT_DAY(TO_DATE(TO_CHAR(SYSDATE,'YYYY')||'0201','YYYYMMdd')-1,'MON')+14 INTO WASHINGTON FROM DUAL;
 SELECT NEXT_DAY(TO_DATE(TO_CHAR(SYSDATE,'YYYY')||'0101','YYYYMMdd')-1,'MON')+14 INTO MLK FROM DUAL;
 SELECT NEXT_DAY(TO_DATE(TO_CHAR(SYSDATE,'YYYY')||'0201','YYYYMMdd')-1,'MON')+14 INTO WASHINGTON FROM DUAL;
 SELECT NEXT_DAY(LAST_DAY(TO_DATE(TO_CHAR(SYSDATE,'YYYY')||'0501','YYYYMMdd'))-7,'MONDAY')INTO MEMORIAL FROM DUAL;
 SELECT NEXT_DAY(TO_DATE(TO_CHAR(SYSDATE,'YYYY')||'1001','YYYYMMdd')-1,'MON')+7  INTO COLUMBUS FROM DUAL;
 SELECT NEXT_DAY(TO_DATE(TO_CHAR(SYSDATE,'YYYY')||'1101','YYYYMMdd')-1,'THURSDAY')+21 INTO THANKSGIVING FROM DUAL;
 IF(TRUNC(V_DT) = TRUNC(MLK) )THEN RET:='T';
 ELSIF(TRUNC(V_DT)=TRUNC(WASHINGTON))THEN RET :='T';
 ELSIF(TRUNC(V_DT)=TRUNC(MEMORIAL) )THEN RET:='T';
 ELSIF(TRUNC(V_DT)=TRUNC(COLUMBUS)) THEN RET:= 'T';
 ELSIF(TRUNC(V_DT)=TRUNC(THANKSGIVING)) THEN RET:='T';
 ELSIF(V_MONTH ='01' AND V_DAY='01') THEN RET:='T';  --NEW YEARS
 ELSIF(V_MONTH='07' AND V_DAY='04') THEN RET:='T';
 ELSIF(V_MONTH='09' AND V_DAY='02') THEN RET:='T';
 ELSIF(V_MONTH='11' AND V_DAY='11') THEN RET:='T';
 ELSIF(V_MONTH='12' AND V_DAY='25') THEN RET:='T';
 END IF;  
 RETURN RET;

END DIS_IS_HOLIDAY;

This next function will find first business day given holiday 

create or replace 
 FUNCTION first_business_day (v_dt DATE)
   RETURN DATE
 IS
   return_dt   DATE;
  BEGIN
   SELECT CASE
         WHEN (CASE
                  WHEN( TO_CHAR (TRUNC (V_DT, 'MM'), 'DY') IN
                          ('SAT', 'SUN'))
                  THEN
                     NEXT_DAY (TRUNC (v_dt, 'MM'), 'MON')
                  ELSE
                     TRUNC (V_DT, 'MM')
                  END) IS NOT NULL AND DIS_IS_HOLIDAY(V_DT)='T'

         THEN
            CASE
               WHEN TO_CHAR (TRUNC (v_dt, 'MM') + 1, 'DY') IN
                       ('SAT', 'SUN')
               THEN
                  NEXT_DAY (TRUNC (v_dt, 'MM') + 1, 'MON')
               ELSE
                  TRUNC (v_dt, 'MM') + 1
            END
         ELSE
            CASE
               WHEN TO_CHAR (TRUNC (v_dt, 'MM'), 'DY') IN ('SAT', 'SUN')
               THEN
                  NEXT_DAY (TRUNC (v_dt, 'MM'), 'MON')
               ELSE
                  TRUNC (v_dt, 'MM')
            END
      END
   INTO return_dt
   FROM DUAL;

 RETURN return_dt;
END;

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231661

No. There is no built-in function. Even if you cut it down to just US holidays, that's still not deterministic. Different states have different holidays, different companies recognize different holidays, and different companies deal with holidays differently (i.e. holidays that fall on a Saturday may get celebrated on the prior Friday, the subsequent Monday, or not at all). Realistically, that means that your organization almost certainly wants a custom table of the holidays that your organization recognizes and when those holidays are celebrated.

Why can't you use a stored procedure for this? You could, of course, simply list out the valid holidays in your SQL query but that would be rather inelegant-- a table of holidays and a stored function that determines whether a date falls on a holiday would seem much more practical.

Upvotes: 6

Related Questions