how to convert a string date to date format in ora

Question

My date value is stored as varchar2 and the value is 15/August/2009,4:30 PM, how to convert this to a proper date format like DD-MM-YYYY.

Answer 1

You need to use the TO_DATE function.

SELECT TO_DATE('01/01/2004', 'MM/DD/YYYY') FROM DUAL;

Answer 2

You can convert a string to a DATE using the TO_DATE function, then reformat the date as another string using TO_CHAR, i.e.:

SELECT TO_CHAR(
         TO_DATE('15/August/2009,4:30 PM'
                ,'DD/Month/YYYY,HH:MI AM')
       ,'DD-MM-YYYY')
FROM DUAL;

15-08-2009

For example, if your table name is MYTABLE and the varchar2 column is MYDATESTRING:

SELECT TO_CHAR(
         TO_DATE(MYDATESTRING
                ,'DD/Month/YYYY,HH:MI AM')
       ,'DD-MM-YYYY')
FROM MYTABLE;