Test Database date format conversion

DROP TABLE test;

CREATE TABLE TEST (C VARCHAR2 (20));

INSERT into TEST VALUES (' April 28 ');

INSERT into TEST VALUES (' April 2 ');

INSERT into TEST VALUES (' December 28 ');

INSERT into TEST VALUES (' December 2 ');

COMMIT;

SELECT

C old,

TRIM (To_char (To_number (SUBSTR (c,1, INSTR (C, ' month ')-1)), ' 00 ')

|| TRIM (To_char (To_number (REPLACE (SUBSTR (C,instr (C, ' month ') +1), ' Day ')), ' xx ') new1

,

To_char (to_date (replace (replace (c, ' month ', '/'), ' Day ', ') ', ' mm/dd '), ' MMDD ') new2

From

TEST;

This article from the "Technical Exchange" blog, declined reprint!

Test Database date format conversion