Oracle PL/SQL RR Datetime Format

The RR datetime format element is similar to the YY datetime format element, but it provides additional flexibility for storing date values in other centuries. The RR datetime format element lets you store 20th century dates in the 21st century by specifying only the last two digits of the year.

If you use the TO_DATE function with the YY datetime format element, then the year returned always has the same first 2 digits as the current year. If you use the RR datetime format element instead, then the century of the return value varies according to the specified two-digit year and the last two digits of the current year

If the two digits of the current year are : If the Specified two digit year is
00-49 50-99
00-49 The return date is in the current century The return date is in the century before the current one
50-99 The return date is in the century after the current one The return date is in the current century
  • 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
Assume these queries are issued between 1950 and 1999

Example 1:

SELECT TO_CHAR(TO_DATE('09-MAR-95', 'DD-MON-RR') ,'YYYY') "Year"  FROM DUAL;

YEAR
-----------
1995

Example 2:

SELECT TO_CHAR(TO_DATE('09-MAR-15', 'DD-MON-RR') ,'YYYY') "Year"   FROM DUAL; 

YEAR
-----------
2015
Assume the above queries are run between 2000 and 2049


Example 1:

SELECT TO_CHAR(TO_DATE('09-MAR-95', 'DD-MON-RR') ,'YYYY') "Year"  FROM DUAL;

YEAR
-----------
1995

Example 2:

SELECT TO_CHAR(TO_DATE('09-MAR-15', 'DD-MON-RR') ,'YYYY') "Year"   FROM DUAL; 

YEAR
-----------
2015
Labels: , , ,