1 2 3 Previous Next 31 Replies Latest reply: Aug 1, 2013 7:55 AM by Etbin Go to original post RSS
      • 30. Re: Bug in julian date
        Hoek

        BluShadow wrote:

        [...]

        What is surprising though is what this thread has shown, that you can create a valid date and then 'shift' it using date arithmetic to create year 0 dates etc. especially as you cannot create year 0 dates directly.  That's certainly an inconsistency in the high level language where you're not consciously trying to bypass validation.

         

        Yes, totally agreed. This thread has become very interesting (quite unexpected).

        Here's (in a nutshell, ofcourse) the fix they're working on, just got another reply from MOS/Metalink:

        "[...] DEV will fix this issue by modifying the source code so that it adds a constraint to verify that year 0 could not be stored even if that was a result of an arithmetic operation [...]"

         

        Oh well, this was fun, but back to date related work again, migrating a database where they decided to store dates as numbers (and yes, lots of 0000's for unknown years) to a database where those numeric dates must be converted to 'real dates' AND strings... *should I laugh or should I cry...*

        • 31. Re: Bug in julian date
          Etbin

          Hoek wrote:

          "[...] DEV will fix this issue by modifying the source code so that it adds a constraint to verify that year 0 could not be stored even if that was a result of an arithmetic operation [...]"

          So we can expect a constraint violation to be thrown even from intermediate results.

          They won't try to avoid the year 0 they can materialize now :


          with

          t as

          (select date '-0001-12-20' the_date,30 the_shift from dual union all

          select date '0001-01-20' the_date,-30 the_shift from dual union all

          select date '-0001-12-20' the_date,400 the_shift from dual union all

          select date '0001-01-20' the_date,-400 the_shift from dual

          )

          select to_char(the_date,'syyyy-mm-dd') ||

                 case when sign(the_shift) >= 0 then ' + ' else ' - ' end ||

                 to_char(abs(the_shift)) ||

                 ' = ' ||

                 to_char(the_date + the_shift,'syyyy-mm-dd') old,

              

                 to_char(the_date,'syyyy-mm-dd') ||

                 case when sign(the_shift) >= 0 then ' + ' else ' - ' end ||

                 to_char(abs(the_shift)) ||

                 ' = '||

                 to_char(case when substr(to_char(the_date,'syyyy'),1,1) !=

                                   substr(to_char(the_date + the_shift,'syyyy'),1,1)

                              then case when substr(to_char(the_date,'syyyy'),1,1) = '-'

                                        then add_months(the_date + the_shift,12)

                                        when substr(to_char(the_date,'syyyy'),1,1) = ' '

                                        then add_months(the_date + the_shift,-12)

                                   end

                              else the_date + the_shift

                         end,

                         'syyyy-mm-dd'

                        ) new

            from t


          Regards


          Etbin

          1 2 3 Previous Next