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

> Oracleguru

Well, aren't we too modest? :-)

>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.

In most installations, Oracle's default is DD-MON-YY, without the time. But this format is only used when entering or printing dates. It has nothing to do with storing the dates. Oracle always stores the dates with a four digit year, and a timestamp. In fact, Oracle does not store the dates as strings at all. The timestamp could be 0:00:00, but it is always present.

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

True. Until we change from 2049 to 2050. Users who by then are used to entering 49 for 2049 will suddenly notice that 49 yields 2149 as of the system date 2050...

>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.

Nor does the RR format. As far as I know, the releases supporting RR also support RRRR. But I could be wrong here.

>As interesting fact, the following yields roman numerals, xcviii.
>
>select to_char(98,'rr') ROMAN_NUMERALS from dual;

Just because you are not using to_char for a date, but for a number.

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

Please no. Why the full time?

> Avoid rrrr format.

Why's that?

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

Well, when inserting dates, RRRR interprets 98 as 1998. YYYY gives you 0098. What do you prefer?

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

I disagree. Using RR in to_char( some date) is exactly the same as using YY. It just depends on the width you wish to use. RR does not yield roman characters when using on dates! By the way, when matching fails Oracle will try other formats anyway. Like specifying four digits for RR will have Oracle use RRRR instead of RR. That is: at least in Oracle 8.

Now, getting back to the question:

>> Is there any way to stop Oracle from implying the century and NOT allow
>> 2 digit dates?

You can use the FX (Format Exact) modifier for exact format checking. FX toggles strict checking on and off.

update my_table
set my_date = to_date( v_MyDate, 'FXDD-MON-YYYY');

This also forces the day and month to have two digits. See the examples on FM (Fill Mode) in your help to solve that.

Arjan. Received on Wed Aug 12 1998 - 14:42:41 CDT