Re: Oracle 7.2 Year2000 & Medium dates

From: Danilo Gimenez <danilog_at_mandic.com.br>
Date: 1997/10/20
Message-ID: <01bcdd5c$8a70a8a0$f7d9f5c8@danilo>#1/1

Hello, Mike.

Mike Desson <mdesson_at_internetwis.com> escreveu no artigo <01bcdbde$5bd5eb60$4ad7e6ce_at_993110h>...
> Can anyone provide a detailed solution on how to set Oracle7.2 properly
 to
> have dates entered in medium date format (01-Oct-20) interpretted as
> 01-October-2020 instead of 01-October-1920.
>

You can add the line "NLS_DATE_FORMAT=DD-MON-RR" or "NLS_DATE_FORMAT=DD-MON-RRRR" to your envorinment, so Oracle will act just like if the century started in 01-JAN-1950 and finished in 31-DEC-2049.

1. In WindowsNT Servers: use REGEDT32.EXE. You have to add the entry above in HKEY_LOCAL_MACHINE - SOFTWARE - ORACLE key.
2. In Windows95 Clients: use REGEDIT.EXE. You have to add the entry above in HKEY_LOCAL_MACHINE - SOFTWARE - ORACLE key.
3. In both Unix Clients or Servers: edit /etc/profile (all users) or $HOME/.profile (for each user) files. You have to add the entry above in one of these two files.

> We have been told to add a line to config.ora NLS_DATE_FORMAT=DD-MON-RR
> and that the maximum date is 01-jan-37 (2037).
>
> Then we heard that the statement was NLS_DATE_FORMAT=DD-MON-RRRR and the
> maximum date is 31-Dec-36.

Nonsense !!! Try yourself in a SQL*Plus session. After logging in, type ALTER SESSION SET NLS_DATE_FORMAT = DD-MON-RRRR. Then try SELECT SYSDATE FROM DUAL. If you want, use the script below:

CREATE TABLE TEST

        (test_time        date,
          what_i_want        varchar2(50));
ALTER SESSION
        SET NLS_DATE_FORMAT = DD-MON-RR;
INSERT INTO TEST   -- value: 01-jan-1950
        VALUES('01-JAN-50', 'It would be 01-jan-1950');
INSERT INTO TEST   -- value: today
        VALUES(sysdate, 'Well, it's today');
INSERT INTO TEST   -- value: 01-jan-1999
        VALUES('01-JAN-99', 'It would be 01-jan-1999');
INSERT INTO TEST   -- value: 31-dec-1999
        VALUES('31-DEC-99', 'It would be 31-dec-1999');
INSERT INTO TEST   -- value: 01-jan-2000
        VALUES('01-JAN-00', 'It would be 01-jan-2000');
INSERT INTO TEST   -- value: 31-dec-2000
        VALUES('31-DEC-00', 'It would be 31-dec-2000');
INSERT INTO TEST   -- value: 01-jan-2001
        VALUES('01-JAN-01', 'It would be 01-jan-2001');
INSERT INTO TEST   -- value: 01-jan-2049
        VALUES('01-JAN-49', 'It would be 01-jan-2049');
INSERT INTO TEST   -- value: 31-dec-2049
        VALUES('31-DEC-49', 'It would be 01-dec-2049');

COMMIT;
SELECT TO_CHAR(test_time, 'DD-MON-YYYY') AS "Is it correct ??", What_I_Want

       FROM TEST; Do not forget to drop table TEST after you're done!

-- 

DANILO GIMENEZ
danilog_at_mandic.com.br
Sao Paulo - SP - Brasil

Received on Mon Oct 20 1997 - 00:00:00 CDT