1 2 3 Previous Next 31 Replies Latest reply: Aug 1, 2013 7:55 AM by Etbin RSS

    Bug in julian date

    KjetilSkotheim

      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

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

          • 2. Re: Bug in julian date
            KjetilSkotheim

            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

              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

                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

                  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

                    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

                      ...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

                        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

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

                          • 10. Re: Bug in julian date
                            bencol

                            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

                              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

                                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

                                  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
                                    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