to_char function issue with date passing in the format of 'dd-mon-yyyy'

Question

My query is

select TO_CHAR('03-JAN-2013', 'D') from dual;

but an error occured as

ORA-01722: invalid number
01722. 00000 -  "invalid number"
*Cause:    
*Action:

But when query changed as select TO_CHAR(sysdate, 'D') from dual;

Result is right answer 5.

I can't understand why it is behaving like this, please help me.

Thanks in advance

Answer 1

Please cast the string to date before selecting.

SELECT TO_CHAR(CAST('03-JAN-2013' AS DATE), 'D') FROM DUAL;

OR

SELECT TO_CHAR(TO_DATE('03-JAN-2013'), 'D') FROM DUAL;