Re: Is oracle 8.0.5 Y2K ready?

Where do I find this NLS_DATE_FORMAT parameter?

Jim

Frank Hubeny <fhubeny_at_ntsource.com> wrote in message news:382BBD7C.F1D7D35_at_ntsource.com...
> A useful reference from the Oracle home site is
>
> http://www.oracle.com/year2000/pdf/server_dhwp.pdf
>
> As I understand it, Oracle stores dates with adequate precision to meet
> Y2K standards, but there is the problem of the NLS_DATE_FORMAT that sets
> the default implicit date-to-string conversion to be 'DD-MON-YY'. This
> default should probably have been set as 'DD-MON-YYYY' long ago by
> Oracle forcing application developers to explicitly override the format
> where necessary. Then any Y2K problem would lie squarely on the
> developers' shoulders.
>
> You can check nls_session_parameters to see what your current value is.
>
> What this means is that if your applications use any implicit
> date-to-string conversions you might have trouble unless you change
> NLS_DATE_FORMAT. However, the problem may not be as easy as adding the
> century digits because this might misalign some reports which have room
> for only 2 digits to be displayed and which rely on the old value of
> NLS_DATE_FORMAT for their format mask.
>
> To see the problem with NLS_DATE_FORMAT set to the current default of
> 'DD-MON-YY', run the following query to get the following inaccurate
> result:
>
> SQL> select to_char(to_date(sysdate + 60),'DD-Mon-YYYY') from dual;
>
> TO_CHAR(TO_
> -----------
> 10-Jan-1900
>
> Why is this future date 100 years in the past? This is because there is
> an implicit date-to-string conversion turning sysdate + 60 into
> '10-JAN-00' to agree with the expected string input for the to_date
> function. This is then converted back to a date using the same
> implicit format which leads to the inaccurate result. If you end up
> storing that result you could be in trouble. Although the example above
> is contrived, developers do use implicit date-to-string conversions
> often without being aware of it.
>
> The point is to avoid implicit date-to-string conversions.
>
> So, is Oracle Y2K compliant? Yes and maybe not.
>
> Yes, because the date storage is adequate. Maybe not, because Oracle
> has allowed implicit date-to-string conversions without generating
> adequate errors and it has done so with a default date mask that is
> clearly inadequate for year 2000. This has helped build up a body of
> buggy code. Of course, Oracle can fault the application developers for
> not using explicit conversions. And they can also fault any dba who
> forgets to reset the default NLS_DATE_FORMAT that Oracle originally
> provided them.
>
> According to the above article, Oracle 8.1.6 will replace the format
> mask 'YY' with 'RR' (page 15). And to Oracle's credit, it does provide
> Y2K information such as the cited article on its web site.
>
> Frank Hubeny
>
>
>
> Keith Kwiatek wrote:
>
> > Hello,
> >
> > Is Oracle 8.0.5 Y2k ready?
> >
> > Keith
>
Received on Tue Nov 16 1999 - 09:03:19 CST