4 Replies Latest reply on Oct 17, 2007 9:50 AM by Turloch O'Tierney-Oracle

    displaying Null-date in SQL developer


      I run into the following problem (with SQL developer Vers. 1.2.0 build 2998).
      On a table with a date column and a null date in some rows (the default value is "to_date(1, 'J')" ), SQL developer is showing after a query in the SQL worksheet the date "01/01/4713"
      However, when I'm executing the same query in SQL*Plus, the columns with a null-date showing the value "01/01/4712"

      How is it possible that SQL-Developer and SQL*Plus showing different dates on the same data?

      Any suggestion or explanations for this behavior?

      Any help is much appreciated.


        • 1. Re: displaying Null-date in SQL developer
          They are both getting the same internal Oracle date format.
          They will use different code to translate those bytes into a visible format.

          The problem is about year 0 because historically there wasn't one.
          The internal date format does permit dates in the year zero, but mostly the SQL interface forbids it.
          For example
          select to_date('01010000','ddmmyyyy') from dual;
          will fail, but
          select to_date('01010001','ddmmyyyy')-1 from dual;
          will pass
          select to_char(to_date('01010001','ddmmyyyy')-5,'dd/mon/yyyy') from dual;
          produces, well, garbage.

          Storing 'dummy' values tends to lead to odd results which is why it is generally frowned upon. Use a null and another column to indicate why a real value isn't appropriate.
          • 2. Re: displaying Null-date in SQL developer
            Thanks for your posting g.myers.

            But IMHO it doesn't explain why the date in SQL*Plus is displayed correctly but not in SQL Developer :-(

            It would go beyond the scope of this thread to explain why we have a date column like this in our date base. We have to live with this fact.

            I was hoping someone from the SQL development team could reply to the post and could indicate whether this is possibly on list bug list or not.

            • 3. Re: displaying Null-date in SQL developer
              It does appear to be an SQL Developer bug. This makes it fairly obvious.
              select to_date('01/01/0001 BC','dd/mm/yyyy BC'),
              to_char(to_date('01/01/0001 BC','dd/mm/yyyy BC'),'DD/Mon/YYYY BC')
              from dual

              But it is a display bug. A workaround is to do the to_char conversion in the database, rather than relying on SQL Developer.

              Of course, to resolve it they'll need to work out what to do with
              select to_date('01/01/0001','dd/mm/yyyy')-5,
              to_char(to_date('01/01/0001','dd/mm/yyyy')-5,'DD/Mon/YYYY BC')
              from dual

              Assuming you have a support contract, and iTar (or whatever it is called now) is the best way to report a bug.
              • 4. Re: displaying Null-date in SQL developer
                Turloch O'Tierney-Oracle
                Reproduced and bug logged: