Converting string to date in Oracle

While converting STRING to a DATE we can follow any of the following syntax:

SELECT TO_DATE (‘NUMERIC_STRING’, 'MMDDYY') FROM TABLE_NAME

SELECT TO_DATE (' NUMERIC_STRING / NUMERIC_STRING / NUMERIC_STRING,'DD/MM/YYYY') FROM TABLE_NAME

SELECT TO_DATE (' NUMERIC_STRING / NUMERIC_STRING / NUMERIC_STRING, 'yyyy/mm/dd') FROM TABLE_NAME

SELECT TO_DATE (' NUMERIC_STRING ', 'MMDDYYYY') FROM TABLE_NAME

NUMERIC_STRING Should be within the range,(1-12 for month,1-31 for day, any four digit number for YYYY, any two digit number for yy).

Default date format for Oracle is mm/dd/yyyy.

Examples to convert character to string in oracle

CREATE TABLE CONVERSION_FUNCTIONS

(

NUMERIC_VALUE INTEGER,

STRING VARCHAR(10 ),

DATE_VALUE DATE

);

INSERT INTO CONVERSION_FUNCTIONS (NUMERIC_VALUE, STRING, DATE_VALUE) VALUES (12091990, 'BBB', to_date('1990-05-06','yyyy-mm-dd'));

INSERT INTO CONVERSION_FUNCTIONS (NUMERIC_VALUE, STRING, DATE_VALUE) VALUES (01031991, 'CCC', to_date('1999-06-07','yyyy-mm-dd'));

INSERT INTO CONVERSION_FUNCTIONS (NUMERIC_VALUE, STRING, DATE_VALUE) VALUES (11121990, 'DDD', to_date('1995-12-18','yyyy-mm-dd'));

SELECT TO_DATE

(

NUMERIC_VALUE,

'MMDDYYYY'

)

FROM

CONVERSION_FUNCTIONS

All Chapters