Re: How to check whether my Oracle database has Y2K problem?

From: Oracleguru, Suresh Bhat <oracleguru_at_mailcity.com>
Date: Thu, 11 Feb 1999 15:54:08 GMT
Message-ID: <01be55e7$2465a4c0$a504fa80@mndnet>

Violin,

You need to change the NLS_DATE_FORMAT by putting in a line like the one given below in your
init.ora file, $ORACLE_HOME/dbs/initXXXX.ora, where XXXX is your SID:

nls_date_format = 'DD-MON-RR'

After you STOP and re-START the database, the new NLS_DATE_FORMAT will be in effect.

RR format puts the dates correctly in the database that are between the years 1950 and 2049.

YY format assumes the dates are all in the current century.

As far as whether you have a Y2K problem, it depends if your programs use TO_CHAR and TO_DATE functions with YY in the format. Also you may have a Y2K problem if you have dates already stored in the database that are prior to 1950 such as employees birthdates etc.

Following is another way to use it just for your SQL*Plus session to play around:

alter session set nls_date_format = 'dd-MON-rr';

Following are the rules that I have come with for our department and have successfully completed and tested the Y2K conversion for over 800 PL*SQL, SQL*Plus, Pro*Fortran, SQL*Forms etc.

Others may have different ideas.

---

Rules for TO_DATE function:  

   FORMAT rr SHOULD BE USED FOR 2 BYTE OR 2 DIGIT YEAR.  

   FORMAT yyyy SHOULD BE USED FOR 4 BYTE OR 4 DIGIT YEAR.  

   DO NOT USE yy format. Avoid rrrr format. There is NO yyrr or rryy format.  

   For 4 BYTE OR 4 DIGIT YEAR, use format yyyy, even though format rrrr works.

   Format rrrr did not work in Release 7.1.

---

Rules for TO_CHAR function:  

   ALWAYS USE yyyy format. yy format works but you may not know the actual    date that is being converted. There is NO yyrr or rryy format.  

   FORMAT rr on a NUMBER field yields Roman numerals. DO NOT USE IT ON    2 BYTE or 2 DIGIT YEAR.

Good lunck !!!

Oracleguru
Suresh Bhat
www.oracleguru.net
oracleguru_at_mailcity.com

Violin <violin.hsiao_at_mail.pouchen.com.tw> wrote in article <36c226fa.258671_at_news.twsc.pouchen.com.tw>...
> Hello,
> Our production Database is Oracle 7.3.2 on NetWare 4.10.
> I connect as internal/password
> select * from props$ where name = 'NLS_DATE_FORMAT';
> And return the date format : DD-MON-YY
> Does this database has Y2K problem?
> If yes,how to change the NLS_DATE_FORMAT?
> Thank you for any help!
>
> Violin.
> violin.hsiao_at_mail.pouchen.com.tw
>
Received on Thu Feb 11 1999 - 09:54:08 CST