Re: Oracle 7.3 and Year 2000

In article <77scpv$gij$1_at_news3.Belgium.EU.net>,   "Patrick Nobels" <patrick.nobels_at_ping.be> wrote:
> 1. You force date entry in 4 digits and set your input mask to the same YYYY
> as the database format. In this case you can not allow input in yy format.
> 2. You use the RR format wich does a century calculation.I don't know the
> exact algoritm but it's something like YY >50 => Actual century
> YY<50 next century...

Even better is to use 'RRRR'. It understands both two digit *and* four digit years. It outputs four digits. In my opinion (and I've said it in this newsgroup often enough!) it's the best format to use.

If you use 'YY' then you will almost certainly have Y2K problems.

If you use 'RR' then you are unable to enter dates more than 50 years in the future, or the past. If you try to do this you will end up with a date in a different century from what you were expecting. Unfortunately you probably won't notice at first, because when selecting the data back you will only see the (correct) last two digits, not the (incorrect) first two! Very nasty!

For new applications, on a 'new' database, 'YYYY' may be OK, but has (at least) one nasty feature: If some old code (or something new written by a programmer used to the 'old' style default date) like this is run: X := to_date('01-JAN- 99'), then X will be in the year '0099'. Not what you want, I'm sure.

If you use 'RRRR' you will still be able to enter dates using only two digits - the same rounding rule as for 'RR' will apply. If you want a date more than 50 years in the past or future, you can specify the whole four digits. In either case, when you select your data back you will see all four digits, so you'll know if your date is correect.

A word of warning first... even the 'RRRR' format can break existing applications that were coded to expect the default 'DD-MON-YY' format. If you don't have time to fix all your code, a possible solution would be to set the database default to 'RRRR', but run your applications with 'RR' by changing the setting on a per-session basis, just after the application connects.

A few examples (using Oracle 7.3.3):

SQL> select to_char(sysdate,'DD-MON-YYYY') from dual; 19-JAN-1999 SQL> alter session set NLS_DATE_FORMAT='DD-MON-YY';

SQL> select to_char(to_date('01-jan-99'),'DD-MON-YYYY') from dual; 01-JAN-1999 SQL> select to_char(to_date('01-jan-01'),'DD-MON-YYYY') from dual; 01-JAN-1901 /* the classic Y2K bug */

SQL> alter session set NLS_DATE_FORMAT='DD-MON-YYYY';

SQL> select to_char(to_date('01-jan-99'),'DD-MON-YYYY') from dual; 01-JAN-0099 /* YUCK!!! - I consider this an Oracle bug. */

SQL> alter session set NLS_DATE_FORMAT='DD-MON-RR';

SQL> select to_char(to_date('01-jan-99'),'DD-MON-YYYY') from dual; 01-JAN-1999 SQL> select to_char(to_date('01-jan-01'),'DD-MON-YYYY') from dual; 01-JAN-2001 SQL> select to_char(to_date('01-jan-49'),'DD-MON-YYYY') from dual; 01-JAN-2049 SQL> select to_char(to_date('01-jan-50'),'DD-MON-YYYY') from dual; 01-JAN-1950 /* a big jump from 2049! */

SQL> select to_date('01-jan-49') from dual; /* use default format */ 01-JAN-49 SQL> select to_date('01-jan-50') from dual; /* use default format */ 01-JAN-50 /* is this 1950 or 2050? - With just two digits, who knows! */

SQL> alter session set NLS_DATE_FORMAT='DD-MON-RRRR';

SQL> select to_date('01-JAN-49') from dual; 01-JAN-2049 SQL> select to_date('01-JAN-50') from dual; 01-JAN-1950 /* at least you can see it! - if you want 2050, use 4 digits! */

SQL> select to_date('01-JAN-2050') from dual; 01-JAN-2050 /* that's better! */

I hope this has answered a few questions.

Regards
Yuri McPhedran

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Tue Jan 19 1999 - 06:00:28 CST