RR vs YY in Oracle

Posted on by Ian

When formatting dates in Oracle Database, we have the option of using RR and YY to return a two digit year.

These two format elements are similar. The difference is in how they interpret two digits years.

We also have the option of using RRRR and YYYY when returning four digit years.

Example

Here’s an example that compares the RR and YY format elements:

SELECT 
    TO_CHAR(TO_DATE('20-Dec-81', 'DD-Mon-RR'), 'YYYY') AS "RR",
    TO_CHAR(TO_DATE('20-Dec-81', 'DD-Mon-YY'), 'YYYY') AS "YY"
FROM DUAL;

Result:

     RR      YY 
_______ _______ 
1981    2081

We can see that RR interprets the year 81 as 1981, while YY interprets it as 2081.

  • When using YY, the year returned always has the same first 2 digits as the current year.
  • With RR, the century of the return value varies according to the specified two-digit year and the last two digits of the current year.

Here’s how the Oracle documentation explains it:

  • If the specified two-digit year is 00 to 49, then
    • If the last two digits of the current year are 00 to 49, then the returned year has the same first two digits as the current year.
    • If the last two digits of the current year are 50 to 99, then the first 2 digits of the returned year are 1 greater than the first 2 digits of the current year.
  • If the specified two-digit year is 50 to 99, then
    • If the last two digits of the current year are 00 to 49, then the first 2 digits of the returned year are 1 less than the first 2 digits of the current year.
    • If the last two digits of the current year are 50 to 99, then the returned year has the same first two digits as the current year.

The RRRR and YYYY Format Elements

Here’s an example that compares the RRRR and YYYY format elements:

SELECT 
    TO_CHAR(TO_DATE('20-Dec-81', 'DD-Mon-RRRR'), 'YYYY') AS "RRRR",
    TO_CHAR(TO_DATE('20-Dec-81', 'DD-Mon-YYYY'), 'YYYY') AS "YYYY"
FROM DUAL;

Result:

   RRRR    YYYY 
_______ _______ 
1981    0081

In this case, RRRR returns the same year that RR did, but YYYY returns the year 0081.

The above examples assume that you know how TO_CHAR() and TO_DATE() work. See Oracle TO_CHAR(datetime) Function and Oracle TO_DATE() Function in Oracle for more info.