Re: Date problems

Cefio wrote:
>
> Hi
>
> I am working under Oracle version 7.3 and i have a table containing a date
> field.
> Since year 2000, all records with an year 99 are interpreted 2099 inspite of
> 1999.
> Can i resolve this problem ??
> If yes where in Oracle must i do the modification ??
>
> Thanks.

The problem is that 7.3 comes out of the box with the init.ora NLS_DATE_FORMAT parameter set to DD-MON-YY. What you appear to expect is for a character string such as 04-FEB-99 to be stored as Feb. 4th, 1999. What DD-MON-YY will do is store it as Feb. 4th, 2099.

You can get what you want by changing the init.ora param to DD-MON-RR.

However, from the fact that you are posting this in October of 2001, I suspect that (1) you have all sorts of dates stored incorrectly in your system, and you don't know it because 20990402 and 19990402 look the same with DD-MON-YY as the date mask, and (2) there are numerous implicit date conversions going on inside your system which you don't know about, and which will clobber you if you simply change the default date mask and don't take care of them.

In short, you really need to do a Y2K conversion of your system.

-- 
Phil Singer                |    psinger1_at_chartermi.net
Oracle DBA