While testing some date/time functions today I came across an interesting quirk in Oracle’s Gregorian calendar and different date representations.
In the Gregorian calendar, the day after October 4, 1582 is October 15, 1582 (not all countries adopted the calendar immediately, so they have different gaps, but Oracle observes the calendar as it was originally defined.) However, depending on the version of the Oracle database, that calendar adjustment is handled differently.
In database versions 12.1.0.2 and lower, TO_DATE can’t be used to create a DATE value within the calendar gap. If you try, the day portion will be changed to October 15, the time will be remain the same. So, as shown in the example below. Attempting to create a date with TO_DATE of October 10, causes the same time to be shifted to October 15. The same will happen with any other day falling into the hole. Unless… you use a date-literal. Then you can create dates within the gap.
SQL> SELECT banner FROM v$version
2 WHERE banner LIKE 'Oracle Database%';
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
SQL> SELECT DATE '1582-10-10' date_literal,
2 TO_DATE('1582-10-10 12:34:56', 'yyyy-mm-dd hh24:mi:ss') date_conversion,
3 TIMESTAMP '1582-10-10 12:34:56' timestamp_literal,
4 TO_TIMESTAMP('1582-10-10 12:34:56', 'yyyy-mm-dd hh24:mi:ss') timestamp_conversion
5 FROM DUAL;
DATE_LITERAL DATE_CONVERSION TIMESTAMP_LITERAL TIMESTAMP_CONVERSION
------------------- ------------------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
1582-10-10 00:00:00 1582-10-15 12:34:56 1582-10-10 12:34:56.000000000 1582-10-15 12:34:56.000000000
In 12.2 and above, the date handling is consistent. All dates in the calendar gap are shifted to October 15.
SQL> SELECT banner FROM v$version
2 WHERE banner LIKE 'Oracle Database%';
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> SELECT DATE '1582-10-10' date_literal,
2 TO_DATE('1582-10-10 12:34:56', 'yyyy-mm-dd hh24:mi:ss') date_conversion,
3 TIMESTAMP '1582-10-10 12:34:56' timestamp_literal,
4 TO_TIMESTAMP('1582-10-10 12:34:56', 'yyyy-mm-dd hh24:mi:ss') timestamp_conversion
5 FROM DUAL;
DATE_LITERAL DATE_CONVERSION TIMESTAMP_LITERAL TIMESTAMP_CONVERSION
------------------- ------------------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
1582-10-15 00:00:00 1582-10-15 12:34:56 1582-10-15 12:34:56.000000000 1582-10-15 12:34:56.000000000
Also interesting is conversion between the two types of date handling in the older versions. While a date literal can be created, if you then try to manipulate it, the gap-date will again be shifted to October 15.
SQL> SELECT date_literal, date_literal + 2 2 FROM (SELECT DATE '1582-10-05' date_literal FROM DUAL); DATE_LITERAL DATE_LITERAL+2 ------------------- ------------------- 1582-10-05 00:00:00 1582-10-17 00:00:00
This can get even more confusing because the shifting will take into account the delta within the gap. In the example above, October 5, is shifted to October 15, then add 2 to get October 17.
But, try a later date and the math is a little weird because it both shifts the date to October 15, but also preserves the difference from within the gap. e.g. October 10 is five days later than October 5, so a shifted date is also 5 days later.
SQL> SELECT date_literal, date_literal + 2 2 FROM (SELECT DATE '1582-10-10' date_literal FROM DUAL); DATE_LITERAL DATE_LITERAL+2 ------------------- ------------------- 1582-10-10 00:00:00 1582-10-22 00:00:00
In 12.2 and above, this difference preservation does not occur. All dates from the gap are converted to October 15, so adding 2 to October 5, or October 10 will still result in October 17, because both of those gap-dates are converted to the same value before applying the date math.
There is another hole in the calendar at year-zero, in that there isn’t one. Oracle even has a specific error message to say so.
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
But, as with the Gregorian calendar hole, date-literal values for the year-0 can be created if you’re using a 12.1.0.2 database or lower.
SQL> SELECT banner FROM v$version 2 WHERE banner LIKE 'Oracle Database%'; BANNER -------------------------------------------------------------------------------- Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production SQL> select date '0000-01-01' from dual; DATE'0000-01-01' ------------------- 0000-01-01 00:00:00
But, try the same thing in 12.2 or above and you’ll get the ORA-1841 error.
SQL> SELECT banner FROM v$version
2 WHERE banner LIKE 'Oracle Database%';
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> select date '0000-01-01' from dual;
select date '0000-01-01' from dual
*
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
For most of applications I expect neither of these holes-in-time to be an issue as most of the data will likely be relatively modern. If you do have centuries- or millennia-old data, or if you’re simply trying to be thorough in your testing of date functions it’s important to note how the calendar has changed over the years, and more importantly, how the database itself handles those changes.