CREATE OR REPLACE FUNCTION f_irt_date_yyyymmdd (ENTER VARCHAR2)
/****
* The Effective date format is YYYYMMDD, the input date format is: 16-jul-15 (date-month-year)
* Example: 20151231
*
*
****/
RETURN VARCHAR2 is
Niendate DATE;
NUM VARCHAR2 (3);
Input_char VARCHAR2 (+): = ENTER;
BEGIN
/*
JAN-01
FEB-02
MAR-03
APR-04
MAY-05
JUN-06
JUL-07
AUG-08
SEP-09
OCT-10
NOV-11
DEC-12
*/
SELECT SUBSTR (input_char,4,3) into NUM from DUAL;
IF NUM = ' JAN ' Then
Niendate: = to_date (' 20 ' | | SUBSTR (input_char,8,2) | | ' 01 ' | | SUBSTR (input_char,1,2), ' YYYYMMDD ');
elsif NUM = ' FEB ' Then
Niendate: = to_date (' 20 ' | | SUBSTR (input_char,8,2) | | ' 02 ' | | SUBSTR (input_char,1,2), ' YYYYMMDD ');
elsif NUM = ' MAR ' Then
Niendate: = to_date (' 20 ' | | SUBSTR (input_char,8,2) | | ' 03 ' | | SUBSTR (input_char,1,2), ' YYYYMMDD ');
elsif NUM = ' APR ' Then
Niendate: = to_date (' 20 ' | | SUBSTR (input_char,8,2) | | ' 04 ' | | SUBSTR (input_char,1,2), ' YYYYMMDD ');
elsif NUM = "may" Then
Niendate: = to_date (' 20 ' | | SUBSTR (input_char,8,2) | | ' 05 ' | | SUBSTR (input_char,1,2), ' YYYYMMDD ');
elsif NUM = ' June ' Then
Niendate: = to_date (' 20 ' | | SUBSTR (input_char,8,2) | | ' 06 ' | | SUBSTR (input_char,1,2), ' YYYYMMDD ');
elsif NUM = ' JUL ' Then
Niendate: = to_date (' 20 ' | | SUBSTR (input_char,8,2) | | ' 07 ' | | SUBSTR (input_char,1,2), ' YYYYMMDD ');
elsif NUM = ' the "Then
Niendate: = to_date (' 20 ' | | SUBSTR (input_char,8,2) | | ' 08 ' | | SUBSTR (input_char,1,2), ' YYYYMMDD ');
elsif NUM = ' SEP ' Then
Niendate: = to_date (' 20 ' | | SUBSTR (input_char,8,2) | | ' 09 ' | | SUBSTR (input_char,1,2), ' YYYYMMDD ');
elsif NUM = ' OCT ' Then
Niendate: = to_date (' 20 ' | | SUBSTR (input_char,8,2) | | ' 10 ' | | SUBSTR (input_char,1,2), ' YYYYMMDD ');
elsif NUM = ' NOV ' Then
Niendate: = to_date (' 20 ' | | SUBSTR (input_char,8,2) | | ' 11 ' | | SUBSTR (input_char,1,2), ' YYYYMMDD ');
elsif NUM = ' DEC ' Then
Niendate: = to_date (' 20 ' | | SUBSTR (input_char,8,2) | | ' 12 ' | | SUBSTR (input_char,1,2), ' YYYYMMDD ');
END IF;
RETURN (niendate);
END F_IRT_DATE_YYYYMMDD;
Date format conversion for Oracle