This content has been marked as final. Show 4 replies
They are both getting the same internal Oracle date format.
They will use different code to translate those bytes into a visible format.
The problem is about year 0 because historically there wasn't one.
The internal date format does permit dates in the year zero, but mostly the SQL interface forbids it.
select to_date('01010000','ddmmyyyy') from dual;
will fail, but
select to_date('01010001','ddmmyyyy')-1 from dual;
select to_char(to_date('01010001','ddmmyyyy')-5,'dd/mon/yyyy') from dual;
produces, well, garbage.
Storing 'dummy' values tends to lead to odd results which is why it is generally frowned upon. Use a null and another column to indicate why a real value isn't appropriate.
Thanks for your posting g.myers.
But IMHO it doesn't explain why the date in SQL*Plus is displayed correctly but not in SQL Developer :-(
It would go beyond the scope of this thread to explain why we have a date column like this in our date base. We have to live with this fact.
I was hoping someone from the SQL development team could reply to the post and could indicate whether this is possibly on list bug list or not.
It does appear to be an SQL Developer bug. This makes it fairly obvious.
select to_date('01/01/0001 BC','dd/mm/yyyy BC'),
to_char(to_date('01/01/0001 BC','dd/mm/yyyy BC'),'DD/Mon/YYYY BC')
But it is a display bug. A workaround is to do the to_char conversion in the database, rather than relying on SQL Developer.
Of course, to resolve it they'll need to work out what to do with
Assuming you have a support contract, and iTar (or whatever it is called now) is the best way to report a bug.
Reproduced and bug logged:
6506986 DATES HANDLED DIFFERENTLY IN SQLPLUS AND WORKSHEET/SQLDEVELOPER