DATIMEand 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
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
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/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 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.
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
(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
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!!!|
DATIMEand the Year 2000