2 Key Difference between YYYY and RRRR in Oracle SQL

Difference between YYYY and RRRR

In this post, we will see the difference between YYYY and RRRR in Oracle SQL. If not already knowing YYYY and RRRR are Date formatting predicates. Actually both of them impact the Year of the date to be presented to the calling program.

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.

When you use YYYY in the date format code, it returns the current year as 4 digits.

While RRRR format returns the 2-digit years in the range 00 to 49 are assumed to be in the current century (ie have the same first two digits as the current year), and years given as 50 through 99 are assumed to be in the previous century.

Statement from the docs on difference between YYYY and RRRR:

RR

Given a year with 2 digits, returns a year in the next century if the year is <50 and the last 2 digits of the current year are >=50; returns a year in the preceding century if the year is >=50 and the last 2 digits of the current year are <50.

RRRR

RRRR is the year rounded. Accepts either 4-digit or 2-digit input. If 2-digit provides the same return as RR. If you don’t want this functionality, simply enter the 4-digit year.

YYYY

YYYY is the 4 digit year and when SYYYY is passed with 4-digit year; “S” prefixes BC dates with “-“.

YYY
YY
Y

Last 3, 2, or 1 digit(s) of year.

So YY understands that you are talking about the current century and will insert 2094 when 94 is entered, and RR will follow the rule that > 50 means last century and insert 1994.

However, if you enter 35, both YY and RR will insert 2035. An important distinction.

Examples

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

Year
----
1998

SELECT TO_CHAR(TO_DATE('27-OCT-21', 'DD-MON-RR') ,'YYYY') "Year"
   FROM DUAL; 

Year
----
2021


select  to_char(to_date('01-01-72','dd-mm-yyyy'),'yyyy') Year from dual;

Year
----
0072


 select  to_char(to_date('01-01-72','dd-mm-rrrr'),'yyyy') Year  from dual;
Year
----
1972

Summary

In summary, there are 2 cases of the YEAR when the queries are issued :

CASE 1: 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.

When 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.

CASE 2: 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.

This oracle link gives some examples

Hope I am able to clear the Difference between yyyy and rrrr and when to use it in your pl SQL programming.

Do check out some PL SQL interview questions

We will be happy to hear your thoughts

      Leave a reply

      Cloud Technologies Blog
      Logo
      Compare items
      • Total (0)
      Compare
      0
      Shopping cart