1 2 Previous Next 16 Replies Latest reply on May 29, 2018 8:08 PM by Glen Conway

    [BUG] SQLDeveloper 18.1 wrong date

    Bgilis

      Hello,

       

      I noticed yesterday SqlDeveloper is not displaying the correct date for pre gregorian dates (from 14/10/1582).

       

      Exemple: SELECT to_date('01/01/0001', 'DD/MM/YYYY') AS d from dual;

       

      Will display 03/01/0001. It works well in sqlplus but not in SqlDeveloper 18.01.

       

      Can you reproduce?

       

      Regards

        • 1. Re: [BUG] SQLDeveloper 18.1 wrong date
          I noticed yesterday SqlDeveloper is not displaying the correct date for pre gregorian dates (from 14/10/1582).

           

          Exemple: SELECT to_date('01/01/0001', 'DD/MM/YYYY') AS d from dual;

           

          Will display 03/01/0001. It works well in sqlplus but not in SqlDeveloper 18.01.

           

          Can you reproduce?

          Reproduce? Sure - that has been known for centuries - since about '15 October 1582' to be exact.

           

          As I'm sure you can understand the 'gregorian' calendar did NOT exist in 'pre gregorian' days.

           

          So it isn't possible to use that calendar and expect dates to be accurate.

           

          See the Wiki

          https://en.wikipedia.org/wiki/Gregorian_calendar#Proleptic_Gregorian_calendar

          Extending the Gregorian calendar backwards to dates preceding its official introduction produces a proleptic calendar, which should be used with some caution. For ordinary purposes, the dates of events occurring prior to 15 October 1582 are generally shown as they appeared in the Julian calendar, with the year starting on 1 January, and no conversion to their Gregorian equivalents.

          So there is no agreed upon way, or even real need, to try to extend Gregorian use to pre-gregorian times.

          • 2. Re: [BUG] SQLDeveloper 18.1 wrong date
            Glen Conway

            It works well in sqlplus but not in SqlDeveloper 18.01

            In sqlplus, we see only what the database does with the TO_DATE conversion (in my limited test case, 15 Oct 1582, as well as the ten preceding days 'removed' by the change from Julian to Gregorian, all convert to 15 OCT 1582), as in

            PreGregorianDateTestOnSQLPlus.jpg

            while in SQL Developer there must be some additional erroneous calendar conversion coming either from Java

            (https://docs.oracle.com/javase/8/docs/api/java/util/GregorianCalendar.html ) or SQL Developer itself where those same ten days get removed again!

            PreGregorianDateTestOnSQLDev.jpg

            This is one of those edge cases that probably affect few users.  I would be surprised to learn that Java is at fault, but if SQL Developer does no extra processing in this area, then that would be my conclusion.

            • 3. Re: [BUG] SQLDeveloper 18.1 wrong date
              Glen Conway

              Update:  I forgot to check if this problem exists on earlier versions of SQL Developer.  As a quick test, I used

              select to_date('04/10/1582', 'DD/MM/YYYY') as d from dual;

              On SQL Developer 17.4 it works just like in sqlplus (04-OCT-1582), but in 18.1 it returns 24-SEP-1582 as noted in my prior post.  Both versions use the basic connection type, JDBC thin, and default JDBC drivers shipped with 17.4 and 18.1, which is the same 12.2.0.1 version

              (JAVAVM_12.2.0.1.0_LINUX.X64_160909) in both cases, and JDK 1.8.0_161.

               

              So it looks like something's up with 18.1.

               

              Edit:  By SQLcl version number

                        24-SEP-1582:   Oracle SQLDeveloper Command-Line (SQLcl) version: 18.1.1.0

                        24-SEP-1582:   Oracle SQLDeveloper Command-Line (SQLcl) version: 18.1.0.0

                        04-OCT-1582:   Oracle SQLDeveloper Command-Line (SQLcl) version: 17.4.0.354.2224

              • 4. Re: [BUG] SQLDeveloper 18.1 wrong date
                Bgilis

                rp0428 a écrit:

                 

                I noticed yesterday SqlDeveloper is not displaying the correct date for pre gregorian dates (from 14/10/1582).

                 

                Exemple: SELECT to_date('01/01/0001', 'DD/MM/YYYY') AS d from dual;

                 

                Will display 03/01/0001. It works well in sqlplus but not in SqlDeveloper 18.01.

                 

                Can you reproduce?

                Reproduce? Sure - that has been known for centuries - since about '15 October 1582' to be exact.

                 

                As I'm sure you can understand the 'gregorian' calendar did NOT exist in 'pre gregorian' days.

                 

                So it isn't possible to use that calendar and expect dates to be accurate.

                 

                See the Wiki

                https://en.wikipedia.org/wiki/Gregorian_calendar#Proleptic_Gregorian_calendar

                Extending the Gregorian calendar backwards to dates preceding its official introduction produces a proleptic calendar, which should be used with some caution. For ordinary purposes, the dates of events occurring prior to 15 October 1582 are generally shown as they appeared in the Julian calendar, with the year starting on 1 January, and no conversion to their Gregorian equivalents.

                So there is no agreed upon way, or even real need, to try to extend Gregorian use to pre-gregorian times.

                Hi,

                 

                a bit rude answer. But anyway...

                 

                I "know" this is a common problem and of course I "can understand" the calendar was not existing before. This is not the point,  and we don't have to push at open doors here, we are all agreed on that.

                 

                As Glen noticed, the problem is the date is not consistent between SqlPlus, SqlDeveloper 17.04 and SqlDeveloper 18.01. Period.

                 

                I'm not going in a debat about how to compute the date. The fact is we have initialisation date in a software using 01/01/0001, so this is a problem.

                 

                This is not what was previously done. We cannot use this version right now.

                 

                Regards

                • 6. Re: [BUG] SQLDeveloper 18.1 wrong date
                  IckyIckyChiMoon

                  I had this same question -- posted a couple of weeks ago:

                   

                  Crazy Date bug (or messed up NLS config ?)

                   

                  Same sort of discussion. It looks like Jeff S answered this thread as a bug. On the earlier thread he just said he would look into it.

                  Nice to at least know that the different behavior in version 18 is not correct.

                   

                  iicm

                  • 7. Re: [BUG] SQLDeveloper 18.1 wrong date
                    thatJeffSmith-Oracle

                    for official help/reporting bugs - go to My Oracle Support

                     

                    I try to triage here as I can

                     

                    FWIW, this is working as expected in SQLcl, so I don't think it's a jdbc thing.

                    • 8. Re: [BUG] SQLDeveloper 18.1 wrong date

                      a bit rude answer.

                      Facts are NOT 'rude' - they have no emotional content at all in, and of, themselves.

                      I "know" this is a common problem and of course I "can understand" the calendar was not existing before. This is not the point,  and we don't have to push at open doors here, we are all agreed on that.

                      Yes - I agree it is a common problem.

                       

                      But I disagree with the rest of your comment. The 'common problem' is that many people, apparently your org also, MIISUSE the functionality. Although you might understand the 'calendar was not existing before' many others don't. It is NOT appropriate to use the Gregorian calendar to try to represent values from before the calendar existed.

                      As Glen noticed, the problem is the date is not consistent between SqlPlus, SqlDeveloper 17.04 and SqlDeveloper 18.01. Period.

                      I totally - but, as my opinion above expressed, that is IRRELEVANT if you use the functionality properly to represent dates in proper range. As the Wiki stated you assume the risk if you try to use tools to solve problems they weren't designed to solve. You can't sue the maker of a wrench if you find it won't remove a screw - wrong tool for the job.

                      The fact is we have initialisation date in a software using 01/01/0001, so this is a problem.

                      And the solution is: DON'T DO THAT!

                       

                      Use a value that is the proper range for the calendar you are using. You can use any date after the calendar was introduced: 01/01/1600, 01/01/4000 and thousands of others.

                       

                      Your org made a BAD choice - you should fix the bug in your code.

                       

                      I can't really sympathize if you think the facts I have posted are 'rude' or are representing some other emotion. They are just facts.

                       

                      Your code has a bug - fix the bug. The Sql Dev team can do whatever they want but if I was the one paying them I would prefer they focus their attention on REAL ISSUES and/or new functionality - not chasing down things that aren't supported by the world at large.

                       

                      I accept you likely disagree with the above - I have no problem at all with that.

                      • 9. Re: [BUG] SQLDeveloper 18.1 wrong date
                        thatJeffSmith-Oracle

                        you can both be right - you shouldn't be using 1/1/0000 as a marker/placeholder in your data model.

                         

                        If the query returns data incorrectly, that's a bug. I kept waiting, however, to hear just why you'd want to query such data...because it is a 'weird' test case.

                        • 10. Re: [BUG] SQLDeveloper 18.1 wrong date
                          cormaco

                          Use a value that is the proper range for the calendar you are using. You can use any date after the calendar was introduced: 01/01/1600, 01/01/4000 and thousands of others.

                          According to the docs the DATE datatype is valid between 4712 BC and 9999 AD:

                          https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Data-Types.html#GUID-7B72E154-677A-4342-A1EA-C74C1EA928E6

                           

                          12

                          DATE

                          Valid date range from January 1, 4712 BC, to December 31, 9999 AD. The default format is determined explicitly by the NLS_DATE_FORMATparameter or implicitly by the NLS_TERRITORY parameter. The size is fixed at 7 bytes. This data type contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It does not have fractional seconds or a time zone.

                          • 11. Re: [BUG] SQLDeveloper 18.1 wrong date
                            Glen Conway

                            you shouldn't be using 1/1/0000

                            Actually you cannot at all.  Having read the aforementioned Wikipedia article (Too Much Information!) on Proleptic_Gregorian_calendar, there is no year 0.

                            SQL> c /0001/0000/
                              1* SELECT to_date('01/01/0000', 'DD/MM/YYYY') AS d from dual;
                            SQL> /
                            
                            Error starting at line : 1 in command -
                            SELECT to_date('01/01/0000', 'DD/MM/YYYY') AS d from dual
                            Error report -
                            ORA-01841: (full) year must be between -4713 and +9999, and not be 0
                            

                             

                            My test case focused on what happened around the Gregorian initialization date (15 Oct 1582), but the OP's test case was actually for 01/01/0001, which works for me on 17.4 but not 18.1.  It's great the policy about logging bugs only on MOS has been relaxed!

                            • 13. Re: [BUG] SQLDeveloper 18.1 wrong date
                              Bgilis

                              thatJeffSmith-Oracle a écrit:

                               

                              you can both be right - you shouldn't be using 1/1/0000 as a marker/placeholder in your data model.

                               

                              If the query returns data incorrectly, that's a bug. I kept waiting, however, to hear just why you'd want to query such data...because it is a 'weird' test case.

                              Hello,

                               

                              Our ERP is using such a date (01/01/0001 NOT 01/01/0000) to avoid nulls and indicate there was no beginning date and 31/12/9999 for end dates.

                              So it may be strange but this is the first piece of software causing problem with this date and we cannot change this code, it's not ours.

                               

                              As cormaco pointed, this is a valid date so I disagree with the analysis of rp0428. This is not a bug in the ERP software. This may be a bad choice for some reason (such the problem arising here) but not a really wrong choice.

                               

                              Anyway, Glad to hear it's a bug and that it will be fixed.

                               

                              Regard

                              • 14. Re: [BUG] SQLDeveloper 18.1 wrong date
                                cormaco

                                Glen Conway schrieb:

                                 

                                you shouldn't be using 1/1/0000

                                Actually you cannot at all. Having read the aforementioned Wikipedia article (Too Much Information!) on Proleptic_Gregorian_calendar, there is no year 0.

                                I give you year zero (executed on livesql):

                                 

                                select TO_CHAR(add_months(date '0001-01-01',-1),'DD.MM.YYYY') from dual
                                
                                00.00.0000
                                
                                1 2 Previous Next