Oracle to_char bug

Question

I don't know if it's a bug or what, but when I try to format the Day of the week in a certain way with the to_char function in Oracle, SQL Plus give me this error : ORA-01821: date format not recognized

Here's the line that cause a problem

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

So d is of 'Day of the week' and sp is for spell. This line should print five because we are thursday.

It's weird because this next line worked

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

dd is for 'Day of the month' so sql plus printed twenty-nine without any problem!!

Can someone tell me why this line is not working?

Thanks..

The sp option to have it spell it out was new to me so I decided to try it out. On Oracle 9i Enterprise 9.2.0.7.0 "select to_char(sysdate, 'dsp') from dual;" gives the result you expected. Unfortunately I do not know why it is not working for you. — dave, Sep 29 '11 at 17:46
I'm using Oracle 10G enterprise (10.2.0.3.0) and get the same behavior as you, Joel — Mike G, Sep 29 '11 at 17:52
Fails for me (I can repeat the OP issue) on 10.2.0.4. Perhaps a 10g issue? — DCookie, Sep 29 '11 at 17:53
I also just tried it out on Oracle 10g enterprise 10.2.0.4.0 - and received the ORA-01821. — dave, Sep 29 '11 at 18:01
Another interesting note: the 'dthsp' and 'dth' format models work. — DCookie, Sep 29 '11 at 18:10
I can also confirm the bug (10.2 XE). It seems like `'dspth'` doesn't work either, although `'dthsp'` does — NullUserException, Sep 29 '11 at 18:32
I get the same error, in 10g. So this may be a bug introduced in 10g that was fixed in 11g. — Dave Costa, Sep 29 '11 at 18:37
@DCookie For Express it is, its kind of home edition anyway :) — mazaneicha, Sep 30 '11 at 22:02
@maz, there may be hoops to jump thru in a production environment — DCookie, Sep 30 '11 at 22:05
Thats my point - nobody uses Oracle Express in prod. Its a stripped down development version. — mazaneicha, Oct 01 '11 at 13:10

score 3 · Accepted Answer · answered Sep 29 '11 at 18:16

3

If you must make this work, here's an ugly workaround:

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

Looks like a bug to me...

answered Sep 29 '11 at 18:16

DCookie

42,630
11
83
92

Oracle to_char bug

1 Answers1