Toad World® Forums

Converting from String to Date

ERROR:
Lookup Error
ORA-01722: invalid number

ERROR:
Lookup Error
ORA-01843: not a valid month

ERROR:
Lookup Error
ORA-01861: literal does not match format string


CODE:
TO_DATE(to_CHAR(AR.KEY_DATA_DATE),‘MM/DD/YYYY’)

Hello-

I pulled out a string of data using a substr from a string of data. The data type of the colum is VARCHAR. The string that I pulled out looks like this ‘2005-05-31’ I want to change this to a date format of ‘05/31/2005’.

I tried a number of things as noted below:

CODE:
TO_CHAR(AR.KEY_DATA_DATE, ‘YYYY/MM/DD’)


CODE:
,TO_DATE(AR.KEY_DATA_DATE, ‘YYYY/MM/DD’)

Can someone help me out?

You have a varchar2 field containing a string like ‘2005-05-31’. You want to change it to a string containing ‘05/31/2005’. This field will contain dates.

Solution:

SQL> select to_char (to_date (‘2005-05-31’, ‘YYYY-MM-DD’), ‘MM/DD/YYYY’)
2 from dual ;

TO_CHAR(TO