YYYY vs. RRRR in Oracle SQL: What's the Difference in Year Formatting?

Year Formatting in Oracle SQL: Understanding the Difference Between 'YYYY' and 'RRRR'

In Oracle SQL, the format masks 'YYYY' and 'RRRR' are used to represent year values in date and time expressions. While they may appear similar at first glance, there are distinct differences between the two.

'YYYY' Format

The 'YYYY' format represents a year in its full four-digit form. It always returns the current year when used in a date or time expression. For example:

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

will return '1987', as it truncates the date to the start of the year in its full four-digit form.

'RRRR' Format

The 'RRRR' format behaves differently from 'YYYY' when handling years in the range of 00 to 99. Years in this range are assumed to follow these rules:

• Years from 00 to 49 are considered to be in the current century (i.e., they have the same first two digits as the current year).
• Years from 50 to 99 are considered to be in the previous century.

For example, executing the following query:

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

will also return '1987', because the year '87' falls within the range from 50 to 99 and is thus assumed to be in the previous century.

Conclusion

While both 'YYYY' and 'RRRR' formats return a four-digit year value, 'RRRR' uses a different logic for years in the range of 00 to 99, considering them in context with the current century. This can be useful for working with historical data or handling year values that may span multiple centuries.

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