Re: Y2K Date Issues with Oracle 7.3.4

The Oracle date format ("yy") does not "window". If you use it in the TO_DATE function, the century portion is defaulted to the current century. Your example below demonstrates this. If you want Oracle to "adjust" the century for you, you need to use "rr" instead of "yy". The "rr" will window the date, adjusting the century.

I.E.
TO_CHAR(TO_DATE('01/06/99','MM/DD/YY'),'MM/DD/YYYY')

TO_CHAR(TO_DATE('01/06/99','MM/DD/RR'),'MM/DD/YYYY')

HTH
    James

In article <26f4216e.e1744044_at_usw-ex0110-075.remarq.com>,   Todd Riley <trileyNOtrSPAM_at_cbsinc.com.invalid> wrote:
> I am having problems with the date format in Oracle 7.3.4.
> There are some sections of our code where, when using the
> to_date function, we have used the two-digit year (e.g.
> to_date('01/11/99','mm/dd/yy') ). I tested this in SQL
> Worksheet using the code below:
>
> select to_char( to_date( '01/06/99' , 'mm/dd/yy' ),
> 'mm/dd/yyyy' ), to_char ( to_date( '01/06/1999' ,
> 'mm/dd/yyyy' ), 'mm/dd/yyyy' )
> from dual;
>
> The result:
>
> TO_CHAR(TO_DATE('01/06/99','MM/DD/YY'),'MM/DD/YYYY')
> -----------------------------------------------------
> 01/06/2099
>
> TO_CHAR(TO_DATE('01/06/99','MM/DD/YYYY'),'MM/DD/YYYY')
> -----------------------------------------------------
> 01/06/1999
>
> Well, the obvious fix is to change to the four-digit date
> format, but that is not the extent of my problem. I know
> the reason this is happening is because according to the
> Oracle book ÿffffc3ÿffffa2ÿffffe2ÿffff82ÿffffacÿffffc5ÿffff9350-99 use
the current century (so 99 becomes
> 1999) while year values of 00-49 are rounded to the next
> century (so 01 becomes 2001). This was obviously written
> in 1999 - so now that the current century is 2000, 99
> becomes 2099. But the big problem that I was having is
> when dates are passed as parameters. For some reason,
> Oracle is still only accepting the two-digit year when
> passed as a parameter and then the above error applies.
> What we have had to do so far is to change the date to a
> string using to_date, pass it as parameter, do what ever
> changes to that variable, and change it back to a date
> value. There has to be an easier way. Any suggestions?
>
> * Sent from AltaVista http://www.altavista.com Where you can also find
related Web Pages, Images, Audios, Videos, News, and Shopping. Smart is Beautiful
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Jan 12 2000 - 22:06:20 CET