Title: Converting Multiple Formats String Dates to Y2K Compliant Dates
Author:Ramakrishna Kolluru, an Oracle DBA for Computer Sciences Corporation, in Dayton, Ohio. The enclosed function converts multiple formats of string dates in any combination of mmddyy,mmddyyyy,monddyy,monddyyyy to DDMONYYYY format. Date separators like /,-, (space)are allowed eg. 21-Dec-99,2000/21/Dec,00 12 Dec.Source/Text/Comments
---------------------------------------------------------
-- Function : new_date(vdate in varchar2)
-- Programmer: Ramakrishna Kolluru DBA,
-- Computer Sciences Corp.
-- Dayton OHIO.
-- Date: Feb 3 , 1999.
-- This function converts multiple formats (mmddyy,mmddyyyy,monddyy,monddyyyy)
-- of string dates into
-- uniform date format DDMONYYYY.
--
-----------------------------------------------------------
CREATE OR REPLACE
FUNCTION new_date (
vdate VARCHAR2
)
RETURN VARCHAR2
IS
vissuedt VARCHAR2(12);
vfound NUMBER;
v3found NUMBER;
v4found NUMBER;
v5found NUMBER;
vyear VARCHAR2(4);
vmonth VARCHAR2(4);
vdte VARCHAR2(4);
BEGIN
IF vdate IS NULL
THEN
RETURN 'X';
END IF;
SELECT TRANSLATE (
UPPER (vdate),
'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890/- ',
'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890'
)
INTO vissuedt
FROM dual;
FOR i IN 1 .. LENGTH (vissuedt)
LOOP
IF SUBSTR (vissuedt, i, 1) IN ('A',
'B',
'C',
'D',
'E',
'F',
'G',
'H',
'I',
'J',
'K',
'L',
'M',
'N',
'O',
'P',
'Q',
'R',
'S',
'T',
'U',
'V',
'W',
'X',
'Y',
'Z')
THEN
vfound := i;
EXIT;
END IF;
END LOOP;
IF LENGTH (vissuedt) = 7
THEN --ddmonRR format
IF vfound = 1 --monddRR format
THEN
SELECT SUBSTR (vissuedt, 1, 3)
INTO vmonth
FROM dual;
SELECT SUBSTR (vissuedt, 4, 2)
INTO vdte
FROM dual;
SELECT SUBSTR (vissuedt, 6, 2)
INTO vyear
FROM dual;
IF vdte <= 31
AND vdte <> '00'
AND vyear <> '00'
THEN
RETURN vdte || vmonth || '19' || vyear;
ELSE
IF vdte = '00'
THEN
RETURN vyear || vmonth || '20' || vdte;
ELSIF vyear = '00'
THEN
RETURN vdte || vmonth || '20' || vyear;
ELSE
RETURN vyear || vmonth || '19' || vdte;
END IF;
END IF;
ELSIF vfound = 3 --ddmonRR format
THEN
SELECT SUBSTR (vissuedt, 3, 3)
INTO vmonth
FROM dual;
SELECT SUBSTR (vissuedt, 1, 2)
INTO vdte
FROM dual;
SELECT SUBSTR (vissuedt, 6, 2)
INTO vyear
FROM dual;
IF vdte <= 31
AND vdte <> '00'
AND vyear <> '00'
THEN
RETURN vdte || vmonth || '19' || vyear;
ELSE
IF vdte = '00'
THEN
RETURN vyear || vmonth || '20' || vdte;
ELSIF vyear = '00'
THEN
RETURN vdte || vmonth || '20' || vyear;
ELSE
RETURN vyear || vmonth || '19' || vdte;
END IF;
END IF;
ELSIF vfound = 5
THEN
SELECT SUBSTR (vissuedt, 5, 3)
INTO vmonth
FROM dual;
SELECT SUBSTR (vissuedt, 1, 2)
INTO vdte
FROM dual;
SELECT SUBSTR (vissuedt, 3, 2)
INTO vyear
FROM dual;
IF vdte <= 31
AND vdte <> '00'
AND vyear <> '00'
THEN
RETURN vdte || vmonth || '19' || vyear;
ELSE
IF vdte = '00'
THEN
RETURN vyear || vmonth || '20' || vdte;
ELSIF vyear = '00'
THEN
RETURN vdte || vmonth || '20' || vyear;
ELSE
RETURN vyear || vmonth || '19' || vdte;
END IF;
END IF;
END IF;
ELSIF LENGTH (vissuedt) = 6
THEN --/dd/mm/RR format
SELECT SUBSTR (vissuedt, 1, 2)
INTO vdte
FROM dual;
SELECT SUBSTR (vissuedt, 3, 2)
INTO vmonth
FROM dual;
SELECT SUBSTR (vissuedt, 5, 2)
INTO vyear
FROM dual;
IF vdte > 90
OR vdte = '00'
THEN
IF vmonth > 12
THEN
RETURN TO_CHAR (TO_DATE (vmonth ||
vyear ||
vdte, 'DDMMRR'), 'DDMONRRRR');
ELSIF vmonth <= 12
AND vyear > 12
THEN
RETURN TO_CHAR (TO_DATE (vyear ||
vmonth ||
vdte, 'DDMMRR'), 'DDMONRRRR');
ELSE
RETURN TO_CHAR (TO_DATE (vmonth ||
vyear ||
vdte, 'DDMMRR'), 'DDMONRRRR');
END IF;
ELSIF vdte > 12
AND vdte <= 31
THEN
IF vmonth > 90
OR vmonth = '00'
THEN
RETURN TO_CHAR (TO_DATE (vdte ||
vyear ||
vmonth, 'DDMMRR'), 'DDMONRRRR');
ELSIF vmonth <= 12
AND vyear > 90
OR vyear = '00'
THEN
RETURN TO_CHAR (TO_DATE (vdte ||
vmonth ||
vyear, 'DDMMRR'), 'DDMONRRRR');
END IF;
ELSIF vdte <= 12
THEN
IF vmonth > 90
OR vmonth = '00'
THEN
RETURN TO_CHAR (TO_DATE (vyear ||
vdte ||
vmonth, 'DDMMRR'), 'DDMONRRRR');
ELSIF vmonth > 12
AND vmonth <= 31
THEN
RETURN TO_CHAR (TO_DATE (vmonth ||
vdte ||
vyear, 'DDMMRR'), 'DDMONRRRR');
ELSIF vmonth <= 12
THEN
RETURN TO_CHAR (TO_DATE (vdte ||
vmonth ||
vyear, 'DDMMRR'), 'DDMONRRRR');
END IF;
END IF;
ELSIF LENGTH (vissuedt) = 8
THEN --/dd/mm/RRRR format
SELECT INSTR (vissuedt, '199', 1, 1)
INTO v3found
FROM dual;
SELECT INSTR (vissuedt, '2000', 1, 1)
INTO v5found
FROM dual;
IF v3found = 1
OR v5found = 1
THEN
SELECT SUBSTR (vissuedt, 1, 4)
INTO vyear
FROM dual;
SELECT SUBSTR (vissuedt, 5, 2)
INTO vmonth
FROM dual;
SELECT SUBSTR (vissuedt, 7, 2)
INTO vdte
FROM dual;
ELSIF v3found = 3
OR v5found = 3
THEN
SELECT SUBSTR (vissuedt, 1, 2)
INTO vdte
FROM dual;
SELECT SUBSTR (vissuedt, 3, 4)
INTO vyear
FROM dual;
SELECT SUBSTR (vissuedt, 7, 2)
INTO vmonth
FROM dual;
ELSE
SELECT SUBSTR (vissuedt, 1, 2)
INTO vdte
FROM dual;
SELECT SUBSTR (vissuedt, 3, 2)
INTO vmonth
FROM dual;
SELECT SUBSTR (vissuedt, 5, 4)
INTO vyear
FROM dual;
END IF;
IF vdte > 12
AND vdte <= 31
THEN
RETURN TO_CHAR (TO_DATE (vdte ||
vmonth ||
vyear, 'DDMMRRRR'), 'DDMONRRRR');
END IF;
IF vdte <= 12
THEN
IF vmonth > 12
THEN
RETURN TO_CHAR (TO_DATE (vmonth ||
vdte ||
vyear, 'DDMMRRRR'), 'DDMONRRRR');
ELSE
RETURN TO_CHAR (TO_DATE (vdte ||
vmonth ||
vyear, 'DDMMRRRR'), 'DDMONRRRR');
END IF;
END IF;
ELSIF LENGTH (vissuedt) = 9
THEN --ddmonRRRR format
IF vfound = 1
THEN
SELECT SUBSTR (vissuedt, 1, 3)
INTO vmonth
FROM dual;
SELECT INSTR (vissuedt, '199', 1, 1)
INTO v4found
FROM dual;
IF v4found = 4
THEN
SELECT SUBSTR (vissuedt, 4, 4)
INTO vyear
FROM dual;
SELECT SUBSTR (vissuedt, 8, 2)
INTO vdte
FROM dual;
RETURN vdte || vmonth || vyear;
ELSE
SELECT SUBSTR (vissuedt, 4, 2)
INTO vdte
FROM dual;
SELECT SUBSTR (vissuedt, 6, 4)
INTO vyear
FROM dual;
RETURN vdte || vmonth || vyear;
END IF;
ELSIF vfound = 3
THEN
SELECT SUBSTR (vissuedt, 3, 3)
INTO vmonth
FROM dual;
SELECT SUBSTR (vissuedt, 1, 2)
INTO vdte
FROM dual;
SELECT SUBSTR (vissuedt, 6, 4)
INTO vyear
FROM dual;
RETURN vdte || vmonth || vyear;
ELSIF vfound = 5
THEN
SELECT SUBSTR (vissuedt, 1, 4)
INTO vyear
FROM dual;
SELECT SUBSTR (vissuedt, 5, 3)
INTO vmonth
FROM dual;
SELECT SUBSTR (vissuedt, 8, 2)
INTO vdte
FROM dual;
RETURN vdte || vmonth || vyear;
ELSIF vfound = 7
THEN
SELECT SUBSTR (vissuedt, 7, 3)
INTO vmonth
FROM dual;
SELECT INSTR (vissuedt, '199', 1, 1)
INTO v4found
FROM dual;
SELECT INSTR (vissuedt, '2000', 1, 1)
INTO v5found
FROM dual;
IF v4found = 1
OR v5found = 1
THEN
SELECT SUBSTR (vissuedt, 1, 4)
INTO vyear
FROM dual;
SELECT SUBSTR (vissuedt, 5, 2)
INTO vdte
FROM dual;
RETURN vdte || vmonth || vyear;
ELSE
SELECT SUBSTR (vissuedt, 1, 2)
INTO vdte
FROM dual;
SELECT SUBSTR (vissuedt, 3, 4)
INTO vyear
FROM dual;
RETURN vdte || vmonth || vyear;
END IF;
END IF;
END IF;
END;
/