This discussion is archived
1 2 3 Previous Next 31 Replies Latest reply: Aug 1, 2013 5:55 AM by Etbin RSS

Bug in julian date

KjetilSkotheim Newbie
Currently Being Moderated

Must be a bug for year zero?

 

select to_date(1721300,'J') from dual

               *

ERROR at line 1:

ORA-01841

  • 1. Re: Bug in julian date
    odie_63 Guru
    Currently Being Moderated

    Year zero does not exist in the Gregorian or Julian calendar. It's not a bug.

  • 2. Re: Bug in julian date
    KjetilSkotheim Newbie
    Currently Being Moderated

    The documentation clearly states "the number of days since January 1, 4712 BC."


    But when I want 1721300 days it doesn't work even if 1722300 and 1720300 does.

     

    Seems Oracle is unable to write dates in the first year or something.

     

    This works:

     

    select to_date('00010101','YYYYMMDD')-1 from dual;

     

    31-DEC-0000

     

    Even if this doesn't:

     

    select to_date('31-DEC-0000','DD-MON-YYYY') from dual;

    ERROR at line 1:

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

  • 3. Re: Bug in julian date
    BluShadow Guru Moderator
    Currently Being Moderated

    This works:

     

    select to_date('00010101','YYYYMMDD')-1 from dual;

     

    31-DEC-0000

     

    Now that is a bug, as it shouldn't do that. As already mentioned there is no year 0 in the calendars, so the julian conversion you are trying is correctly reporting an invalid date, just as when you try to do it with a gregorian date explicitly.

    There are several ways of bypassing date validation in oracle.  Just because you can, doesn't mean you should.  Why do you want to create a year 0 date anyway if it's not valid data?

  • 4. Re: Bug in julian date
    Hoek Guru
    Currently Being Moderated

    And this works as well:


    SQL> select date '0000-01-01' from dual;

    DATE'0000-01-01'
    -------------------
    01-01-0000 00:00:00

    1 row selected.

     

    It is not consistent behavior, one can say...

     


  • 5. Re: Bug in julian date
    bencol Pro
    Currently Being Moderated

    Data Types "Oracle Database uses the astronomical system of calculating Julian days". Astronomical calendars have a year 0, so in an astronomical calendar the difference between 31-Dec-0001 BC and 01-Jan-0001 AD is 367 days (Year 0 is a leap year), rather than 1 (as in most other calendars) and these are included in the Julian Calendar "Days since January 1, 4712 BC". However you cannot convert these numbers (Julian dates 1721058-1721423) to non-astronomical calendars, (reliably).

  • 6. Re: Bug in julian date
    Hoek Guru
    Currently Being Moderated

    Thanks very much

     

    SQL> select to_char(date '0000-01-01','J')
      2  from   dual;

    TO_CHAR
    -------
    0000000

    1 row selected.


  • 7. Re: Bug in julian date
    padders Pro
    Currently Being Moderated

    ...and

     

    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

    With the Partitioning, OLAP, Data Mining and Real Application Testing options

     

     

    SQL> SELECT DATE '0000-01-01' d

      2  FROM   dual;

     

     

    D

    ---------

    01-JAN-00

     

     

    SQL> SELECT TO_CHAR (DATE '0000-01-01', 'DD-MON-YYYY') d

      2  FROM   dual;

     

    D

    -----------

    00-000-0000

     

     

    SQL>

  • 8. Re: Bug in julian date
    bencol Pro
    Currently Being Moderated

    Hoek, padders,

     

    I think the date literal is using the astronomical calendar, so has a year 0


    BANNER
    ________________________________________________________________________________
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    PL/SQL Release 11.2.0.3.0 - Production
    CORE    11.2.0.3.0      Production
    TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
    NLSRTL Version 11.2.0.3.0 - Production

     

    > select date '0001-01-01' - date '-0001-12-31' from dual;

     

    DATE'0001-01-01'-DATE'-0001-12-31'
    __________________________________
                                   367

     

    > select date '0001-01-01' - date '0000-12-31' from dual;

     

    DATE'0001-01-01'-DATE'0000-12-31'
    _________________________________
                                    1

  • 9. Re: Bug in julian date
    padders Pro
    Currently Being Moderated

    ...and why does the TO_CHAR return 00-000-0000?

  • 10. Re: Bug in julian date
    bencol Pro
    Currently Being Moderated

    Because it isn't reliable? I think is a bug that it doesn't return ORA-01841, but I don't think many applications care.

  • 11. Re: Bug in julian date
    Hoek Guru
    Currently Being Moderated

    Agreed, the ORA-01841 OP is getting is not a bug, it is documented behavior (year 0 is excluded):  http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements001.htm#BABFDAEI

    However, the other 'experimental queries' do not return that error.

     

     


  • 12. Re: Bug in julian date
    KjetilSkotheim Newbie
    Currently Being Moderated

    I was writing a sub-query to give me every day in history from 4712BC in order to view the count of events on each of them, even uneventful ones with zero events:

     

    alter session set nls_date_format='SYYYY-MM-DD';

     

    select to_date(rownum,'J') from dual connect by rownum<=to_char(sysdate,'J');

     

    This outputs almost up till year 0 and oddly stops at Dec 29, not Dec 31:

     

    -4712-01-01

    -4712-01-02

    .

    .

    .

    -0001-12-28

    -0001-12-29

    ERROR:

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

    1721055 rows selected.




    This however worked as I wanted:


    select rownum-1+to_date(1,'J') from dual connect by rownum<=to_char(sysdate,'J');


    .......2456505 rows selected.



  • 13. Re: Bug in julian date
    Etbin Guru
    Currently Being Moderated

    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

    Might be interesting: there was no year 0, but rows get generated using:

     

    select date '0001-01-05' - level the_date,

           to_char(date '0001-01-05' - level,'dd.mm.syyyy') the_char_date,

           dump(date '0001-01-05' - level) the_dump

      from dual

    connect by level <= 375

     

    Regards

     

    Etbin

  • 14. Re: Bug in julian date
    Hoek Guru
    Currently Being Moderated
    SQL> select * from v$version;
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    PL/SQL Release 11.2.0.3.0 - Production
    CORE    11.2.0.3.0      Production
    TNS for Linux: Version 11.2.0.3.0 - Production
    NLSRTL Version 11.2.0.3.0 - Production
    5 rows selected.
    SQL> drop table t purge;
    Table dropped.
    SQL> create table t (dt date);
    Table created.
    SQL> insert into t (dt) values (trunc(to_date('0001','yyyy') - 365, 'yyyy'));
    1 row created.
    SQL> select * from t;
    DT
    -------------------
    01-01-0000 00:00:00
    1 row selected.

     

     

    Another bug? What d'y'all say?

1 2 3 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points