CERN Accelerating science

This website is no longer maintained. Its content may be obsolete. Please visit for current CERN information.

next up previous
Next: General Up: cnl228.html Previous: DATIME and the Year 2000

ORACLE and the Year 2000

  Catherine Delamare IT/ASD

Database management systems do not escape the threat of a time bomb waiting to explode when we humans are out celebrating the turn of the century! Let's consider the implications for application systems that use an Oracle database.

Oracle has a DATE data-type that is designed to hold a date and time. The range of dates supported by this data-type is from "01-JAN-4712 BC" to "31-DEC-4712 AD".

Internally, Oracle holds a date as a seven-byte integer that represents the date in packed format with one byte each representing the century, year, month, day, hour, minute and second. Given this internal format, Oracle implicitly records the century in all dates that are held in DATE columns.

The main problem with software written for an Oracle database is the conversion of dates between internal and external(display) format. Oracle provides a mask facility to allow the programmer to specify the required external date format. Examples of masks are: "DD Month YYYY", "DD-MON-YY", "DD/MM/YY" and so on. Problems are bound to arise when the year component of the mask omits the century, since it is always assumed to be 19.

Oracle had the foresight to anticipate the problem as early as 1993 when Oracle7 was first released and the mask "RR" introduced: RR assumes the twentieth century if the year is greater than or equal to 50, and the twenty-first century if it is less than 50. Thus,
TO_DATE('14-JAN-05','DD-MON-RR') yields a date in the year 2005
TO_DATE('21-MAR-68','DD-MON-RR') yields a date in the year 1968.

Unfortunately, not so many designers and programmers have picked up the significance of this new technique, and use of "RR" in date masks, or enforcement of the "YYYY" mask component have not emerged as standards yet.

What do you need to do to sort out the problem and ensure a smooth passage into the new millennium? First of all, do not underestimate the load of work involved. The task of locating all source code and checking every line for a potential problem might be a labour-intensive process. There are a number of tools coming onto the market that claim to automate the process; we remain dubious and have yet to learn of any that are specifically targeted at the Oracle marketplace.

The conclusion to be drawn is that it is highly unlikely that we can avoid digging up and delving into the old application code of our Oracle systems. Each case must be evaluated on its own merits. We cannot go blindly replacing all occurrences of "YY" with "RR". (First we would have to ascertain if we can correctly assume that any date in which the year is less than 50 belongs to the twenty-first century. Also, we certainly cannot replace all instances of "YY" with "YYYY" without carefully considering the effect of the increase in size when making an assignment to a variable or screen field.)

Oracle has provided a very detailed account of the compliance status for each of their many products; you may find this information at the following URL:

Last but not least, do not forget that a good test strategy is key to the success of the entire exercise.

To put the millennium challenge vis-a-vis Oracle in a nutshell:

It is never too early to start planning and investigating. Try to assess the scale of the problem within your Oracle applications. Try to work methodically, identifying areas of risk, fixing them and testing them. You'll then deserve to be able to sleep or party in complete confidence on New Year's Eve 1999!!!

next up previous
Next: General Up: cnl228.html Previous: DATIME and the Year 2000