Re: Y2k problem with 7.3.4

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 02 Mar 1999 18:24:31 GMT
Message-ID: <36e72c25.25088885@192.86.155.100>

A copy of this was sent to "Carl A. Dotger" <dotman23_at_fast.net> (if that email address didn't require changing) On Tue, 2 Mar 1999 11:59:57 -0500, you wrote:

>When selecting back date related data from a table, Oracle seems to natively
>order dates with respect to a two digit year. For Example:
>
>select end_date from period_setup
>order by end_date
>
>
>The above SQL returns something like
>
>1-Jan-00
>1-Jan-98
>1-Jan-99
>
>Obviously, the '00' entries pertain to the year 2000 so this result set is
>incorrect.

Huh? why obviously. To me it means that you have 1900 in your database, not 2000. Consider:

SQL> create table t ( d date );
Table created.

SQL> insert into t values ( to_date( '01-JAN-1900' ) );
SQL> insert into t values ( to_date( '01-JAN-1998' ) );
SQL> insert into t values ( to_date( '01-JAN-1999' ) );
SQL> insert into t values ( to_date( '01-JAN-2000' ) );

SQL> select d from t order by d;

01-JAN-00
01-JAN-98
01-JAN-99
01-JAN-00

SQL> select to_char( d, 'dd-mon-yyyy' ) from t order by d;

TO_CHAR(D,'

01-jan-1900
01-jan-1998
01-jan-1999
01-jan-2000

> Oracle support says that using the century specific date mask
>(ie. 'DD-MON-RR') will return the correct results and it really does seem to
>work. The problem is that we have much source code that would have to

RR is an INPUT format mask and will not change data on the way out. What do you mean 'it really seems to work', what works?

>change. I am interested in the lowest impact solution to this issue. Please

change all date input fields and output fields in all applications to YYYY. It looks like you might already have bad data in your database (based on your conclusion that the date must be 2000 when in fact you will find it to be 1900 or some other year much less then 2000)..

>Help!
>
>TIA,
>
>Carl Dotger
>
>
>
>

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Mar 02 1999 - 12:24:31 CST