If your applications use only two-digit values for years, you may encounter problems related to years around 2000. If you only specify two digits of a year (such as “98” for “1998”), you are relying on the database to specify the century value (the “19”) when the record is inserted. If you are putting in dates prior to the year 2000 (for example, birth dates), you may encounter problems with the century values assigned to your data.
In Oracle, all date values have century values. If you only specify the last two digits of the year value, Oracle will, by default, use the current century as the century value when it inserts a record. For example, the following listing shows an INSERT into the BIRTHDAY table:
In the preceding example, no century value is specified for the BIRTHDATE column, and no age is specified. If you use the TO_CHAR function on the BIRTHDATE column, you can see the full birth date Oracle inserted—it defaulted to the current century:
For dates that can properly default to the current century, using the default does not present a problem. Alicia’s BIRTHDATE value is 21-NOV-2049—wrong by 100 years! Wherever you insert date values, you should specify the full four-digit year value. That way, there is no ambiguity and you don’t need to rely on a specific NLS setting being in place when your SQL statements run on another database.
If you are going to support dates from multiple centuries, consider using the “RR” date format instead of “YY.” The “RR” format for years will pick the century value based on the year. Therefore, years in the first half of the century will have “20” for a century whereas year values in the last half of the century will have “19” for a century value. The use of the “RR” date format can be specified at the database level or the SQL statement level (such as during insert operations).
Leave a Reply