What's the Difference Between 'YYYY' and 'RRRR' Year Formats in Oracle SQL?

Distinguishing 'YYYY' and 'RRRR' Formats in Oracle SQL

In Oracle SQL, the 'YYYY' and 'RRRR' format both represent the year component of a date. However, there exists a subtle difference between the two:

The 'YYYY' format represents the year as a four-digit integer. It always considers the supplied year value as the full four-digit year, regardless of its length.

On the other hand, the 'RRRR' format handles two-digit year values differently. When a two-digit year (e.g., 87) is provided, 'RRRR' checks the current year's first two digits. If the provided two-digit year is between 00 and 49, it is considered to be part of the current century. Conversely, if the two-digit year falls between 50 and 99, 'RRRR' interprets it as belonging to the previous century.

To illustrate this difference, consider the following example:

select trunc(to_date('27-Jul-1987'),'YYYY') FROM dual;

In this case, the result will be '1987', as 'YYYY' expects the year to be represented as a full four-digit integer.

Now, if we use the 'RRRR' format:

select trunc(to_date('27-Jul-1987'),'RRRR') FROM dual;

The result will also be '1987', because the two-digit year '87' falls within the current century (i.e., the first two digits of the current year).

However, if we change the year value to a two-digit year in the previous century, the results differ:

select trunc(to_date('27-Jul-47'),'YYYY') FROM dual;

This query will return '0047' because 'YYYY' always interprets the supplied year as a four-digit integer.

select trunc(to_date('27-Jul-47'),'RRRR') FROM dual;

In contrast, 'RRRR' will interpret the two-digit year '47' as belonging to the previous century and return '1947'.

The above is the detailed content of What's the Difference Between 'YYYY' and 'RRRR' Year Formats in Oracle SQL?. For more information, please follow other related articles on the PHP Chinese website!