April 17, 2024

date, error, format, oracle, short, SQL

ORA-01835 day of week conflicts with Julian date - This means your weekday in input is wrong.


This error message is also a bit confusing, especially if you were not using a Julian date at all; the error messages probably reflects how Oracle calculates dates.

You are likely to encounter this error when you are do testing or some how construct a string to be parsed by to_date or to_timestamp. It just means that you specified an incorrect weekday for a date. Like today, April 17, 2024 is a Wednesday.

This works:

select to_date('Wed Apr 17 2024','Dy Mon dd yyyy')
from dual;

Specifying wrong weekday leads to the error above:

select to_date('Tue Apr 17 2024','Dy Mon dd yyyy')
from dual;
01835. 00000 -  "day of week conflicts with Julian date"
*Cause:    A Julian date was specified with the day of the week,
           but the weekday did not correspond to the Julian date. If the
           day of the week was specified with a Julian date, it must be
           the same day of the week as the Julian date.
*Action:   Remove the day of the week value from the date
           specification or enter the correct day of the week for the
           Julian date.

Of course you can just parse the date string without the weekday. I encountered this error while writing an adapter that parses timestamps from Enterprise Manager. When sending notifications to a webhook it uses a format nobody else does, and certainly not ISO-8601. My adapter parses the timestamp before it forwards the payload to another REST API. Of course my adapter had bugs, and while testing different date strings I changed the date, but not the weekday.

For other short explanations on DATE problems, see ORA-01861 and ORA-01821