Search code examples
databaseoracle-databasey2k

Did Oracle face the Y2K problem?


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?


Solution

  • 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 to the date mask, which derives a century on the basis of a date 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.