Oracle Date Formats And The Year 2000

Note: According to Oracle, the changes on the server and client-side are automatically performed starting with Oracle 8.1.6, also referred to as Oracle 8i Release 2. Check your individual operation system installation release notes for more details.

Purpose

This document describes how to modify 1) the init.ora file of your database server and 2) your Windows client. These modifications allow Oracle to interpret the two-digit year correctly.

Modify The Server INIT.ORA File

The init.ora file is the Oracle database initialization file. The name of this file can vary, but throughout the Oracle documentation, it is typically referred to as the init.ora or init.ora file. Depending on the Oracle installation, this file is often called initorcl.ora or some similar name the Oracle database administrator assigned.

Following are the steps to modify the file.

1. Make a backup copy of the current init.ora file in case something goes wrong.
2. Edit the init.ora file, check if a parameter called NLS_DATE_FORMAT="DD-MON-RR" exists. If not, add the line to the file and save it. Shutdown and restart your database.

An excerpt of an example init.ora file is listed below for your reference:

##############################################################################
...
db_files = 1024
NLS_DATE_FORMAT="DD-MON-RR"
control_files = C:\orant\DATABASE\ctl1ORCL.ora
# db_file_multiblock_read_count =  8 # INITIAL
...
##############################################################################

Modify The Windows Client

a) If you have a 32-bit Oracle client

These instructions are only relevant for machines running an Oracle 32-bit client (Windows 95, NT, etc.).

1. Click on START
2. Run REGEDIT
3. Backup your registry by choosing the Export Registry File option under the Registry menu.
4. Check if the entry already exists. You can use the Find option under the View menu to accomplish this. The entry should read: NLS_DATE_FORMAT "DD-MON-RR"
5. If you don't already have an entry, you can create one as follows: expand on HKEY_LOCAL_MACHINE
6. Expand on SOFTWARE
7. Expand on ORACLE
8. Create a new string name for NLS_DATE_FORMAT and edit the value. It should read DD-MON-RR. Click on OK. Do not enter quotes around this entry, they'll be added automatically!
9. Restart your machine.

View this image at full size (872 px by 517 px, 19 kb).

b) If you have an Oracle 16-bit client

Although Windows NT, Win95 etc. can run 32-bit clients, some users may use Oracle 16-bit applications, such as Oracle Financials. Oracle 16-bit clients do not use the Windows registry, instead you need to modify the oracle.ini file. You should only have one oracle.ini file and the location is determined by the WIN.INI section entry for [ORACLE].

[ORACLE]
ora_config=c:\oraclexxx\oracle.ini

The oracle.ini file (often c:\orawin16\oracle.ini) should contain an entry like the following:

[Oracle]
NLS_DATE_FORMAT=DD-MON-RR