Tuesday, April 05, 2011

Year 0 in Oracle

While troubleshooting a customer's date-related issue, it turned out he had a year 0000 date in his table. He showed me this query of his data looking good, however two-digit years always smell fishy to me:

SQL> select trunc(min(time_stamp_h), 'MONTH') as t_min from mytable;


At any rate the issue was resolved, but I couldn't even figure out how year 0 would have gotten in the database in the first place. Oracle seems to have a validation that does not allow year 0:

SQL> select to_date('9/1/0000', 'mm/dd/yyyy') from dual;
select to_date('9/1/0000', 'mm/dd/yyyy') from dual
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

But...This blog post revealed that there's more to the story - (Short summary: Oracle doesn't always check for year 0. Only sometimes.)

No comments: