All Products
Search
Document Center

RR datetime format element

Last Updated: Jun 18, 2021

The RR datetime format element is similar to the YY datetime format element. However, the RR element offers additional flexibility for storing the values of dates that are not covered by the current century. In the YY datetime format element, you must specify all of the digits of the year. In the RR datetime format element, you need only to specify the last two digits of the year to store a date value.

Assume that the RR datetime format element is used in conjunction with the TO_DATE function. In this case, the century of the return value varies based on the specified two-digit year and the last two digits of the current year. If the YY datetime format element is used in conjunction with the TO_DATE function, the returned year always has the same first two digits as the current year.

Assume that the specified two-digit year ranges from 00 to 49. In this case, if the last two digits of the current year ranges from 00 to 49, the returned year has the same first two digits as the current year. If the last two digits of the current year ranges from 50 to 99, the first two digits of the returned year indicate the next century.

Assume that the specified two-digit year ranges from 50 to 99. In this case, if the last two digits of the current year ranges from 00 to 49, the first two digits of the returned year indicate the previous century. The previous century is the one before the century of the current year. If the last two digits of the current year ranges from 50 to 99, the returned year has the same first two digits as the current year.

The RR datetime format element returns the same value for the years whose first two digits are different, as shown in the following examples: Assume that these queries were run during the period from 1950 to 1999. Execute the following statement:

SELECT TO_CHAR(TO_DATE('27-OCT-98', 'DD-MON-RR'), 'YYYY') "Year1" ,
TO_CHAR(TO_DATE('27-OCT-17', 'DD-MON-RR'), 'YYYY') "Year2"  FROM DUAL;

The following query result is returned:

+-------+-------+
| Year1 | Year2 |
+-------+-------+
| 2017  | 1998  |
+-------+-------+

Assume that these queries were run during the period from 2000 to 2049. Execute the following statement:

SELECT TO_CHAR(TO_DATE('27-OCT-98', 'DD-MON-RR'), 'YYYY') "Year1" ,
TO_CHAR(TO_DATE('27-OCT-17', 'DD-MON-RR'), 'YYYY') "Year2"  FROM DUAL;

The following query result is returned:

+-------+-------+
| Year1 | Year2 |
+-------+-------+
| 2017  | 1998  |
+-------+-------+
Notice

The same value is returned regardless of whether queries were run before or after 2000.