how to convert a string date to date format in oracle10g

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.

score 33 · Accepted Answer · answered Dec 06 '11 at 05:38

33

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;

answered Dec 06 '11 at 05:38

Jeffrey Kemp

1

Thank you. I some how managed to get this result using substring function. But your answer is more simple than than i used. Thank you very much. My sql statement is select to_date(substr(CRM_ACC_DATE_TIME,1,instr(CRM_ACC_DATE_TIME,',')-1),'dd-mm-yyyy') from crm_claim_int_details_view; – leelavinodh Dec 06 '11 at 12:21
1

Yep, there's usually more than one way to skin a cat :) – Jeffrey Kemp Dec 07 '11 at 06:58

score 13 · Answer 2 · edited Feb 22 '17 at 15:06

13

You need to use the TO_DATE function.

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

edited Feb 22 '17 at 15:06

Ahmed Ashour

answered Dec 03 '11 at 08:52

PenFold

1

i am getting this error msg ORA-01858: a non-numeric character was found where a numeric was expected ORA-02063: preceding line from GENCONAIMSUAT 01858. 00000 - "a non-numeric character was found where a numeric was expected" *Cause: The input data to be converted using a date format model was incorrect. The input data did not contain a number where a number was required by the format model. *Action: Fix the input data or the date format model to make sure the elements match in number and type. Then retry the operation. – leelavinodh Dec 03 '11 at 09:46

2 Answers2