ORA-01835: day of week conflicts with Julian date

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

This is a very funny error code, especially when you get it while processing someone else's XML documents.

I had to read the message three or four times before I finally figured out what the real problem was, however I won't bother you with my own problems, so let's go straight to the core question:
what's wrong with the following date conversion?

select to_date('Wed, 23 Aug 2010 13:04:44','Dy, DD Mon YYYY HH24:MI:SS') d
from dual;

ORA-01835: day of week conflicts with Julian date

Come on, it's easy...
23rd of August didn't occur on wednesday. It was monday.

This is an excellent example of what over-engineering date formats can lead to.
Don't ask me why some day someone decided that this overly verbose timestamp format should be taken as "the standard" for RSS timestamps.

How do we fix it?
Well, I guess there are not many choices left other than stripping the first 5 characters containing the 3-letter day of week and the trailing comma and space.

select to_date(substr('Wed, 23 Aug 2010 13:04:44',6),'DD Mon YYYY HH24:MI:SS') d
from dual;

Even more funny when I realized that the server serving these funny timestamps is the Oracle Magazine RSS service!


See message translations for ORA-01835 and search additional resources.