Home » RDBMS Server » Server Administration » Y2K Date Format Issues with Oracle 7.3.4
Y2K Date Format Issues with Oracle 7.3.4 [message #370636] Wed, 12 January 2000 18:33 Go to next message
Todd Riley
Messages: 1
Registered: January 2000
Junior Member
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 50-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. I also know about using dd/mm/rr instead of yy to give you the correct range. 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?
Re: Y2K Date Format Issues with Oracle 7.3.4 [message #370640 is a reply to message #370636] Thu, 13 January 2000 09:22 Go to previous message
Paul
Messages: 164
Registered: April 1999
Senior Member
Todd,
Your current solution (convert to string - reconvert to date) is probably the most bullet proof method. My (admittedly limited) understanding of how parameters work in Oracle leads me to believe that no matter what you think you are passing, you are always passing a character string. If this is true, then any solution based on assumptions as to which century a two character year belongs to will fail if the data does not match the assumption. Oracle does have a white paper available on OTN that deals with Y2K date issues in Developer. This paper is named 'Oracle Developer Year 2000 white paper'. If you are an Oracle Technology Network member, it is located in Documentation - Developer - General Documentation Release 2.0 (if you are not already a member, its worth signing up - costs nothing and makes some useful information available). I was sent a copy of this by a gentleman name Wim Jans after having similar problems where I work. If you wish, you can email me at pberetta@warwick.net and I can forward it to you (it's about 350K in .PDF format). Unfortuantly, our installation is 16 bit Developer, so not as many options as later versions, but it may contain some alternative(s) of benefit to your situation.
Hope this helps,
Paul
Previous Topic: Re: Looking for a good Sql reference book.
Next Topic: URGENT Syntax question.
Goto Forum:
  


Current Time: Sat Oct 31 20:35:28 CDT 2020