'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.

sqloracledate


Solution 1:[1]

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;

Solution 2:[2]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 user2225399
Solution 2 Sun

Related Questions