Did Oracle face the Y2K problem?

Question

My guess is that it shouln't have because they use centuries also in dates

From here,

The DATE datatype stores the year (including the century), the month, the day, the hours, the minutes, and the seconds (after midnight).

Did it face the problem?

Answer 1

Yes it looks like they did:

http://news.cnet.com/Oracle-offers-free-Y2K-upgrade/2100-1001_3-222123.html

Not sure if they faced the exact one you are referring to though.

Answer 2

Yes, Oracle was affected by the Y2K bug. Prior to Oracle 7 the database did not store the century. Backwards compatibility meant that Oracle 7 database used DD-MON-YY as the default format mask for dates. And if you create a date using that mask the century defaults to the current century. Which still leaves problems with dates from the previous century now or dates in the next century then. Strictly speaking this is an application issue rather than a storage issue.

As a work around for this Oracle introduced the RR element oof the date mask, which spawns a generated century of the basis of a window. This was intended for display purposes. Of course, this workaround has become an embedded feature now, and leads to all sorts of problems of its own. Not least because applications used it as an input format mask instead of requiring users to explicitly enter a century.

Anyway, here is how it works.

SQL> insert into t72 values (1, to_date('12-MAY-32', 'DD-MON-YY'))
  2  /

1 row created.

SQL> insert into t72 values (2, to_date('12-MAY-99', 'DD-MON-YY'))
  2  /

1 row created.

SQL> insert into t72 values (3, to_date('12-MAY-50', 'DD-MON-YY'))
  2  /

1 row created.

SQL> insert into t72 values (11, to_date('12-MAY-32', 'DD-MON-RR'))
  2  /

1 row created.

SQL> insert into t72 values (12, to_date('12-MAY-99', 'DD-MON-RR'))
  2  /

1 row created.

SQL> insert into t72 values (13, to_date('12-MAY-50', 'DD-MON-RR'))
  2  /

1 row created.

SQL> insert into t72 values (14, to_date('12-MAY-49', 'DD-MON-RR'))
  2  /

1 row created.

SQL>

The table contents:

SQL> alter session set nls_date_format = 'DD-MON-YYYY'
  2  /

Session altered.

SQL> select * from t72
  2  /

        ID D
---------- -----------
         1 12-MAY-2032
         2 12-MAY-2099
         3 12-MAY-2050
        11 12-MAY-2032
        12 12-MAY-1999
        13 12-MAY-1950
        14 12-MAY-2049

7 rows selected.

SQL>

Years 1-49 are assigned 19 and 0, 50-99 are given 20.

---

It bears repeating that in Oracle the Y2K bug is an application issue not a storage one. Every application in existence will still allows users to write dates as 14-OCT-09 is perpetuating the bug. To the extent that the RR mask encourages this laziness it has made things worse.

Answer 3

Possibly slightly off-topic, but....

I was working for Oracle Support over the Y2K period, including the roll-over night itself.

We got one call all night - a customer asking for a copy of Oracle's Y2K statement. Bit late methinks. :)

Other than that, don't recall receiving any calls on Y2K problems. (Note that I didn't work in the RDBMS Server group though)

Answer 4

As APC said, Oracle has stored dates in a full date-time format since V7, and most client applications also corrected any use of explicit -YY format masks some time before the 2K deadline.

However, I have seen bugs occurring since 2K where people have slipped back into re-using -YY format masks, and not noticing in testing because all their test data is post-Y2K - particularly when doing date/string/date manipulations - an artificial example :

TO_DATE(TO_CHAR(a_date_column,'DD-MM-YY')||'12:00','DD-MM-YYHH24:MI')

This kind of logic is quite common if you are dealing with a legacy system that stores date and time as separate database columns. The syntax may be Oracle specific, but the problem is really a general programming one.

Where I have seen problems that relate more to Oracle has been around NLS date settings. I've seen a DBA rebuild a database but setting the default format back to -YY, and I've also seen errors caused where a JDBC connection was setting the session format to -YY, inherited from the OS environment, and overriding the database default.

Neither of these are faults with Oracle's software, it just pays to be aware that 'Y2K' problems will be around as long as systems and programming languages allow 2-digit years.