Re: 4-digit years

From: Van Messner <vmessner_at_netaxis.com>
Date: Sun, 1 Aug 1999 20:47:34 -0400
Message-ID: <x%5p3.8577$lg.493000@typ12.nn.bcandid.com>

Here's a summary of the year 2000 problem in terms of Oracle and a few tests to try for others who are just starting out:

Oracle Datatype The date datatype in Oracle can be changed by setting a parameter called nls_date_format. Oracle's default DD-MON-YY, places a 19 in front of all two-digit year values and is programmed to place a 20 in front of all two-digit years after the year 2000. This is the default setting, even now. In the sample init.ora files provided by Oracle only some of the hundreds of parameters are shown. Nls_date_format parameters are not shown - so many sites are running with Oracle's default setting of DD-MON-YYYY. nls_date_format accepts the following four values: DD-MON-YYYY Four-digit years are required for query and date storage when using this value setting. This is where you want to be. DD-MON-YY Two-digit years are used for query and date storage, and the current century is aded to the date at insert time. DD-MON-RR Two digit years are used for query and date storage. Where the year ranges from 00 through 49, a 20 is added for the century. For values 50 through 99, a 19 is added for the century. DD-MON-RRRR Four digit years are used for query and date storage. But two digit years are also accepted. For two digit years, Oracle adds 20 for the century when the year ranges from 00 to 49 and it adds 19 for the century when the year ranges from 50 through 99. This is the best choice for an intermediate date format while you get all your years into YYYY format.

The nls_date_format can be changed at the session level by using the Alter Session command which is useful when testing. Once a fix is made it can be changed permanently in the init.ora file. You can use these two views from server manager (and likely SQL/Plus) to see what the setting is:

Select * From V$NLS_PARAMETERS;
Select * From NLS_SESSION_PARAMETERS;
Before beginning the project, our database produced these answers (which, as it turns out, are the Oracle defaults) from the above selects.

NLS_LANGUAGE   AMERICAN
NLS_TERRITORY   AMERICA
NLS_CURRENCY   $
NLS_ISO_CURRENCY  AMERICA
NLS_NUMERIC_CHARACTERS  .,
NLS_DATE_FORMAT   DD-MON-YY
NLS_DATE_LANGUAGE  AMERICAN
NLS_CHARACTERSET  US7ASCII
NLS_SORT   BINARY
NLS_CALENDAR   GREGORIAN

And for the session:

NLS_LANGUAGE   AMERICAN
NLS_TERRITORY   AMERICA
NLS_CURRENCY   $
NLS_ISO_CURRENCY  AMERICA
NLS_NUMERIC_CHARACTERS  .,
NLS_DATE_FORMAT   DD-MON-YY
NLS_DATE_LANGUAGE  AMERICAN
NLS_SORT   BINARY
NLS_CALENDAR   GREGORIAN

2. Oracle Display
There are many ways to change the date display within Oracle. Built in functions such as to_char and to_date can manipulate the display of dates. They alter display but not the internal representation of an Oracle date. But nls_date_format can change the way the century part of a date is stored in an Oracle table.
The default for nls_date_format is DD-MON-YY. Here's what can happen early in the year 2000.

Select count(*) From Shpmnt
Where Shipmnt_Date > '15-JAN-97';

No rows selected.

That's because just after New Year's Day 2000, Oracle will add 2000 to the 97 producing a year of 2097 and no shipments will exist for 2097. Most people would simply change the query to Select count(*) from Shpmnt
Where Shipmnt_Date > '15_JAN-1997';

Error: ORA-01830......

That's because the nls_date_format default calls for a two-digit year but you used a four-digit year. Most people would then go and change the nls_date_format to use a four-digit year format. If you do this query will produce the correct answer (which is 412)(because I say so, that's why). Select count(*) from Shpmnt
Where Shipmnt_Date > '15_JAN-1997';

412

Unfortunately, any query that looks like the one below will now give you a wrong answer. Even worse, note that you don't get an Oracle error message - you get a wrong answer without knowing it's wrong! Select count(*) From Shpmnt
Where Shipmnt_Date > '15-JAN-97';

1768563

So in order to get the right answer we must specify four-digit years in our queries and change nls_date_format in the init.ora file. However we can't change the nls_date_format in our init.ora file until all the SQL accessing the database is converted to use four-digit years in the queries.

3. Oracle Date Insertion
If you insert a date with the Oracle default nls_date_format, (DD-MON-YY), then the two-digit year
will be prefixed with a century of 19 and you will not be allowed to specify a century in your insert. For example: Insert Into Shipmnt_Date Values ('01-JAN-2001');

Error: ORA-01830

If you change the nls_date_format to YYYY you can specify a century in your SQL insert statements. In fact you must specify a century. If you change to YYYY and insert a two-digit year you get a wrong answer - not an Oracle error.:
Insert Into DT Values ('01-JAN-91');

Shipmnt_Date

01-JAN-0091 Which is certainly not what you want. So you better have triggers set to prevent the attempted insertion of any two digit years.

4. Oracle - Looking at the Database
This script can help find what your year values currently look like. Set Pages 9999;
Set Heading Off;
Set Feedback Off;
Spool checkdate.sql;

Select 'spool_date_list.lst' From DUAL;

Select 'Select distinct to_char(' || column_name || ',' 'YYYY'') From '|| owner ||'.'|| table_name || ';' From DBA_TAB_COLUMNS
Where data_type = 'DATE'
And owner NOT IN ('SYS' , 'SYSTEM');

Select 'spool off' From DUAL;

Spool Off;

@checkdate

5. Oracle - The Three Choices
The simplest choice is to change the nls_date_format to DD-MON-RR. As long as you store no dates before 1950 or after 2050 this solution will work. But there are a couple of disadvantages. First there is a certain amount of confusion because the years 00 to 49 are higher than the years 50 through 99. People don't think that way and that increases the risk of bad data or bad answers. Also there will be problems if our database sends, receives or interchanges data with other systems that are not using this approach. That will be most other systems.
The next viable choice is to change the nls_date_format to DD-MON-YYYY. This will require changing all existing SQL to specify four-digit years. We will have to scan all stored procedures, functions, views, and triggers to find dates and to change them to four-digit years. We will have to go through PowerBuilder and InfoMaker reports and windows as well. And we will have to be sure our CASE tool is set to four-digit years. All of this is obviously more work. But it produces the best end solution and this is what I recommend.
The final choice is to change nls_date_format to DD-MON-RRRR. This will handle either two or four-digit years. Two digit years are treated the same way as they are if you use the RR format above. This is a good intermediate step that we can take right away and live with while we gradually change everything over to the YYYY format. Surprisingly, this format is not documented in any of my Oracle documentation. But I have tested it on our 7.2 database and it works.

Dana Reed <danareed_at_oro.net> wrote in message news:37A08CBC.9B9F9D3E_at_oro.net... Received on Sun Aug 01 1999 - 19:47:34 CDT