Oracle error to_char

Question

I don’t know if it was an error or what, but when I try to format the Day of the week in a specific way using the to_char function in Oracle, SQL Plus gives me this error: ORA-01821: date format is not recognized

Here is the line causing the problem

SELECT TO_CHAR(sysdate,'dsp') from dual; 

So d is the "Day of the Week" and sp is for the spell. This line should print five, because we are on Thursday.

This is weird because this next line worked

 SELECT TO_CHAR(sysdate,'ddsp') from dual; 

dd for "Day of the month", so sql plus printed twenty-nine without any problems!

Can someone tell me why this line is not working?

Thanks..

Answer 1

If you have to do this work, here is an ugly workaround:

 SELECT to_char(to_date(to_char(SYSDATE,'d'),'j'),'jsp') FROM dual; 

Looks like a mistake to me ...