CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Taniya
Taniya

Reputation: 121

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

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

Upvotes: 5

Views: 20453

Answers (2)

TechDo
TechDo

Reputation: 18629

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;

Upvotes: 3

Nick Krasnov
Nick Krasnov

Reputation: 27251

The '03-JAN-2013' string literal must be converted to the date data type before invoking TO_CHAR function:

select TO_CHAR(to_date('03-JAN-2013', 'dd-MON-YYYY'), 'D') as res
 from dual

RES
-----
 5

Upvotes: 2

Related Questions