Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to check whether my Oracle database has Y2K problem?

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:55:53 GMT
Message-ID: <01be55e7$63ade660$a504fa80@mndnet>


Oracle does not really store 2 digit year.

It all depends what is set in your initXXXX.ora for nls_date_format attribute.
Oracle's default is 'dd-MON-yy hh24:mi:ss'. YY format simply assumes that the year is in the current century and prepends 19 to the two digit year.

There is another format rr. It works on two digit years between 1950 and 2049 and correctly prepends and stores the 4 digit year.

There is no yyrr or rryy format, what you will get is 9898. Even though rrrr format works, it may not work in older versions of 7.0 Oracle release.

As interesting fact, the following yields roman numerals, xcviii.

select to_char(98,'rr') ROMAN_NUMERALS from dual;


In your case you can have your DBA set it to 'dd-MON-yyyy hh24:mi:ss'. This should work for all centuries.

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

alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';



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:55:53 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US