Thursday, December 04, 2008

Oracle date goodies in SQL/PLSQL

Originally published 03/12/08...

I have been searching through Google, Yahoo! and various forums to create a small library of useful functions and crud for Oracle PL/SQL and SQL. I was either unhappy with the solutions I found or simply thought I could do them better, differently or somewhat the same.

Leap Year

(3-EXTRACT(MONTH FROM TRUNC(date_column,'YEAR')+59)) -- returns 1 if a leap year, 2 if not; from DATE datatype
(3-EXTRACT(MONTH FROM ADD_MONTHS('01-JAN-0001',numeric_year_column*12)-307)) -- returns 1 if a leap year, 2 if not; from NUMBER datatype
DECODE(EXTRACT(MONTH FROM TRUNC(date_column,'YEAR')+59),2,'True','False') -- substitute VARCHAR2 values for the result

Holidays


CREATE OR REPLACE FUNCTION is_holiday(i_date DATE) RETURN VARCHAR2 IS
v_date DATE;
v_y NUMBER;
v_q NUMBER;
BEGIN
v_date := TRUNC(i_date,'Y');
v_y := EXTRACT(YEAR FROM i_date);
v_q := TRUNC(MOD(v_y / 38 * 1440,60) / 2);
RETURN CASE i_date
WHEN v_date THEN 'New Years'
WHEN NEXT_DAY(ADD_MONTHS(v_date,1) - 1,'MON') + 14 THEN 'Presidents Day' --- Third Monday in February
WHEN NEXT_DAY(ADD_MONTHS('01-JAN-0001',v_y * 12 - 8) + v_q + (CASE WHEN v_q < 5 THEN 17 ELSE -12 END),'SAT') - 34 + (CASE v_y WHEN 2079 THEN 7 ELSE 0 END) THEN 'Easter' --- valid 1900-2199 only
WHEN NEXT_DAY(ADD_MONTHS(v_date,5) - 1,'MON') - 7 THEN 'Memorial Day' --- last Monday of May
WHEN ADD_MONTHS(v_date,6) + 3 THEN 'Independance Day'
WHEN NEXT_DAY(ADD_MONTHS(v_date,8) - 1,'MON') THEN 'Labor Day' --- first Monday in September
WHEN ADD_MONTHS(v_date,10) + 10 THEN 'Veterans Day'
WHEN NEXT_DAY(ADD_MONTHS(v_date,10) - 1,'THU') + 21 THEN 'Thanksgiving' --- fourth Thursday in November
WHEN ADD_MONTHS(v_date,11) + 24 THEN 'Christmas'
ELSE NULL
END;
END;
/
The above function appears to be fairly accurate. I tested 2000-2008 for all holidays and 1900-2199 for Easter (finding only one glitch for 2079 manually corrected with a CASE statement). The above function could prove handy if you have a time dimension in your data warehouse (who doesn't) and use PL/SQL to generate your data.

Easter



Uses the Meeus/Jones/Butcher Gregorian algorithm
FUNCTION GET_EASTER2_FN(i_year NUMBER) RETURN DATE IS
v_g PLS_INTEGER; -- the Golden number; sequence in the 19-year lunar cycle
v_c PLS_INTEGER; -- the Christian era i.e. century
v_y PLS_INTEGER; -- the year number in the Christian era
v_e PLS_INTEGER; -- the epact; days in excess of the solar year over the lunar year
v_l PLS_INTEGER; -- the ?
BEGIN
v_g := MOD(i_year,19);
v_c := TRUNC(i_year / 100);
v_y := MOD(i_year,100);
v_e := MOD((19 * v_g + v_c - TRUNC(v_c / 4) - TRUNC((v_c - TRUNC((v_c + 8) / 25) + 1) / 3) + 15),30);
v_l := MOD(32 + 2 * (MOD(v_c,4) + TRUNC(v_y / 4)) - v_e - MOD(v_y,4),7);
RETURN ADD_MONTHS('22-MAR-0001',(i_year - 1) * 12) - TRUNC((v_g + 11 * v_e + 22 * v_l) / 451) * 7 + v_e + v_l;
END;

Format Independant TO_DATE

FUNCTION TO_DATE_FN( i_datechar VARCHAR2 ) RETURN DATE IS
v_datechar VARCHAR2(32000) := UPPER(TRIM(i_datechar));
BEGIN
RETURN CASE TRANSLATE(NVL(v_datechar,'N/U/L/L'),'0123456789/-ABCDEFGJLMNOPRSTUVY','##########/-^^^^^^^^^^^^^^^^^^^')
WHEN '^/^/^/^' THEN NULL
WHEN '########' THEN TO_DATE(v_datechar,'YYYYMMDD')
WHEN '######' THEN TO_DATE(v_datechar,'MMDDYY')
WHEN '##/##/##' THEN TO_DATE(v_datechar,'MM/DD/YY')
WHEN '##/##/####' THEN TO_DATE(v_datechar,'MM/DD/YYYY')
WHEN '####-##-##' THEN TO_DATE(v_datechar,'YYYY-MM-DD')
WHEN '##-^^^-##' THEN TO_DATE(v_datechar,'DD-MON-YY')
WHEN '##-^^^-####' THEN TO_DATE(v_datechar,'DD-MON-YYYY')
ELSE TO_DATE('01-JAN-2989','DD-MON-YYYY')
END;
END TO_DATE_FN;

More to come...

2 comments:

bd said...

Hi, thanks for GET_EASTER2_FN- I saved a lot of time thanks to you! By the way- use
ADD_MONTHS(to_date('22-03-0001','dd-mm-yyyy')
and I'm not sure if shouldn't be:
to_dsinterval() instead of date arithmetic?

Grouchy said...

You are technically correct with the use of TO_DATE() with a format instead of assuming everyone uses the same default format and then have Oracle do implicit conversion. Implicit conversion is usually always bad.

Check another one of my blog posts regarding intervals. I do not know if the ANSI error throw could happen in this situation but ADD_MONTHS is safer and generated accurate results back to the first Easter.

Thanks for the input.