Re: Dates in Oracle.. a Y2K question....

From: Oracleguru <oracleguru_at_mailcity.com>
Date: Tue, 11 Aug 1998 13:56:29 GMT
Message-ID: <01bdc53f$80d9a280$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
www.oracleguru.net   

Shelley LaPorte <pixelp_at_sinclair.net> wrote in article <35D02BAA.9739D71A_at_sinclair.net>...
> Is there any way to stop Oracle from implying the century and NOT allow
> 2 digit dates?
> Rather than programatically handling it on the client side, is there a
> way of creating a stored package or procedure or "something"
> which will not allow the entry of 2-digit dates within the database
> itself??
>
> Any help would be greatly appreciated.
>
>
Received on Tue Aug 11 1998 - 08:56:29 CDT