Search code examples
oracleoracle11g

Oracle 11.1 bug converting Julian day number to DATE or TIMESTAMP

This recent post got me busy investigating Julian date conversions in Oracle, and I've come across what I believe is a bug in Oracle 11.1. Test cases are:

Case 1.

SELECT TO_CHAR(TO_TIMESTAMP('0', 'J'), 'DD MON SYYYY') FROM DUAL

This should return "01 JAN -4713" as defined here, but instead raises the error

ORA-01854: julian date must be between 1 and 5373484

Case 2.

SELECT TO_CHAR(TO_TIMESTAMP('1', 'J'), 'DD MON SYYYY') FROM DUAL

This should return "02 JAN -4713" as an extension of the above (one day later than the Julian zero date), but instead returns "01 JAN -4712" (off by a day less than a year).

Case 3.

SELECT TO_CHAR(TO_TIMESTAMP('1721424', 'J'), 'DD MON SYYYY') FROM DUAL

returns "01 JAN 0001". That's fine (as far as it goes). If we then subtract 1 from the date value above we would expect it to return the previous day, i.e. 31 DEC -0001 (year zero does not exist); however, when we execute the following

SELECT TO_CHAR(TO_TIMESTAMP('1721423', 'J'), 'DD MON SYYYY') FROM DUAL

the following error is thrown:

ORA-01841: (full) year must be between -4713 and +9999, and not be 0

indicating that Oracle has attempted to generate a year of zero.

(Note that although TO_TIMESTAMP is used in the test cases above, the exact same problems occur when TO_DATE is used).

Does anyone know if

  1. These problems have been documented by Oracle?
  2. These problems still exist in 11.2?

Share and enjoy.

Per Phil's answer below, these issues still occur in 11.2.

Cthulhu fhtagn.

Same bugs in 10.2.0.4

Solution

  • When trying to see what Oracle is supposed to do, look at Oracle's documentation, "A Julian day number is the number of days since January 1, 4712 BC."

    That wording does seam to imply that Julian 1 would be one days since January 1, 4712 BC, in other words January 2. However the current implementation of Julian date calculation has been in place for a long time, with existing code depending on the behavior. (I know we'd be screwed if the definition of Julian implemented in Oracle were to change.) At this point it would be at most a documentation bug to be days since December 31, 4713 BC.

    EDIT Found a reference for Julian 1 being January 1, in Call Interface Programmer's Guide. Not someplace normal database programmers would ever look at.

    The following explains the year difference between wikipedia and Oracle:

    Oracle Database uses the astronomical system of calculating Julian days, in which the year 4713 BC is specified as -4712. The historical system of calculating Julian days, in contrast, specifies 4713 BC as -4713. If you are comparing Oracle Julian days with values calculated using the historical system, then take care to allow for the 365-day difference in BC dates. For more information, see http://www.usno.navy.mil/USNO/astronomical-applications/astronomical-information-center/millennium.

    Case 3 is news to me. Thank you for bringing it up. I do not know of any reference covering that behavior. Related:

    SQL> select to_date('0001-01-01', 'YYYY-MM-DD') 
    - to_date ('-0001-12-31', 'SYYYY-MM-DD') from dual;

TO_DATE('0001-01-01','YYYY-MM-DD')-TO_DATE('-0001-12-31','SYYYY-MM-DD')
-----------------------------------------------------------------------
                                                                    367

    and

    SQL> select months_between(to_date('0001-01-01', 'YYYY-MM-DD')
  2      , to_date ('-0001-12-31', 'SYYYY-MM-DD')) from dual;

MONTHS_BETWEEN(TO_DATE('0001-01-01','YYYY-MM-DD'),TO_DATE('-0001-12-31','SYYYY-MM-DD'))
---------------------------------------------------------------------------------------
                                                                             12.0322581

    Apparently the non existent year 0 is a leap year.