Y2K Date Issues with Oracle 7.3.4

From: Todd Riley <trileyNOtrSPAM_at_cbsinc.com.invalid>
Date: Wed, 12 Jan 2000 10:38:00 -0800
Message-ID: <26f4216e.e1744044_at_usw-ex0110-075.remarq.com>

[Quoted] 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 [Quoted] 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

Received on Wed Jan 12 2000 - 19:38:00 CET