How Oracle Interprets Two-Digit Years

The Oracle database stores all four digits of the year, but if you only supply two digits Oracle will interpret the century depending on whether the YY or RR format is being used.

Tip	I recommend you always specify all four digits of the year when supplying a date to an Oracle database. That way, you won t get confused as to which year you mean.

Let s take a look at the YY format first, followed by the RR format.

Using the YY Format

If your date format uses YY for the year and you supply the two digits of a year, the century for your year is assumed to be the same as the present century currently set on your database server. Therefore, the first two digits of your supplied year are set to the first two digits of the present year. For example, if your supplied year is 15 and the present year is 2006, your supplied year is set to 2015. Also, 75 is set to 2075.

Note	If you use the YYYY format but only supply a two-digit date, your date is interpreted using the YY format.

Let s take a look at a query that uses the YY format when interpreting the years 15 and 75. In the following example, notice that the input dates 15 and 75 are passed to TO_DATE(), whose output is passed to TO_CHAR(), which converts the dates to a string with the format DDMONYY YY. (I use YYYY so you can see all four digits of the year returned by TO_DATE().)

  SELECT   TO_CHAR(TO_DATE('04-JUL-15', 'DD-MON-YY'), 'DD-MON-YYYY'),   TO_CHAR(TO_DATE('04-JUL-75', 'DD-MON-YY'), 'DD-MON-YYYY')   FROM dual;  TO_CHAR(TO_ TO_CHAR(TO_ ----------- ----------- 04-JUL-2015 04-JUL-2075

As expected, 15 and 75 are interpreted as 2015 and 2075.

Using the RR Format

If your date format is RR and you supply the last two digits of a year, the first two digits of your year are determined using the two-digit year you supply (your supplied year ) and the last two digits of the present date on your database server (the present year ). The rules used to determine the century of your supplied year are as follows :

Rule 1 If your supplied year is between 00 and 49 and the present year is between 00 and 49, the century is the same as the present century. Therefore, the first two digits of your supplied year are set to the first two digits of the present year . For example, if your supplied year is 15 and the present year is 2005, your supplied year is set to 2015.
Rule 2 If your supplied year is between 50 and 99 and the present year is between 00 and 49, the century is the present century minus 1. Therefore, the first two digits of your supplied year are set to the present year s first two digits minus 1 . For example, if your supplied year is 75 and the present year is 2005, your supplied year is set to 1975.
Rule 3 If your supplied year is between 00 and 49 and the present year is between 50 and 99, the century is the present century plus 1. Therefore, the first two digits of your supplied year are set to the present year s first two digits plus 1 . For example, if your supplied year is 15 and the present year is 2075, your supplied year is set to 2115.
Rule 4 If your supplied year is between 50 and 99 and the present year is between 50 and 99, the century is the same as the present century. Therefore, the first two digits of your supplied year are set to the first two digits of the present year . For example, if your supplied year is 55 and the present year is 2075, your supplied year is set to 2055.

Table 4-3 summarizes these results.

Table 4-3: How Two-Digit Years Are Interpreted
		Two-Digit Supplied Year
		00-49	50-99
Last Two Digits of Present Year	00-49	Rule 1: First two digits of supplied year are set to first two digits of present year	Rule 2: First two digits of supplied year are set to present year's first two digits minus 1
	50-99	Rule 3: First two digits of supplied year are set to present year's first two digits plus 1	Rule 4: First two digits of supplied year are set to first two digits of present year

Note	If you use the RRRR format but only supply a two-digit date, your date is interpreted using the RR format.

Let s take a look at a query that uses the RR format when interpreting the years 15 and 75. For the following example, you should assume the present year is 2005.

  SELECT   TO_CHAR(TO_DATE('04-JUL-15', 'DD-MON-RR'), 'DD-MON-YYYY'),   TO_CHAR(TO_DATE('04-JUL-75', 'DD-MON-RR'), 'DD-MON-YYYY')   FROM dual;  TO_CHAR(TO_ TO_CHAR(TO_ ----------- ----------- 04-JUL-2015 04-JUL-1975

As expected from rules 1 and 2, 15 and 75 are interpreted as 2015 and 1975. For the next example, you should assume the present year is 2075.

  SELECT   TO_CHAR(TO_DATE('04-JUL-15', 'DD-MON-RR'), 'DD-MON-YYYY'),   TO_CHAR(TO_DATE('04-JUL-55', 'DD-MON-RR'), 'DD-MON-YYYY')   FROM dual;  TO_CHAR(TO_ TO_CHAR(TO_ ----------- ----------- 04-JUL-2115 04-JUL-2055

As expected from rules 3 and 4, 15 and 75 are interpreted as 2115 and 2055.