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