Understanding the yy vs rr Date Masks in Oracle SQL

When working with date formats in Oracle SQL, a common question arises: What is the difference between Oracle’s yy and rr date masks? Understanding this difference can dramatically affect the results returned by your queries, especially when dealing with two-digit year formats. Let’s break down these date masks and clarify why they produce different results.

The Problem Explained

In Oracle SQL, when performing date manipulations or comparisons, you may encounter situations where you need to convert strings into date formats. The yy and rr date masks are often used for this purpose.

Here are two examples that demonstrate their differences:

SELECT ename FROM emp WHERE hiredate = TO_DATE('01/05/81', 'dd/mm/yy');

SELECT ename FROM emp WHERE hiredate = TO_DATE('01/05/81', 'dd/mm/rr');

When executed, these two queries may return different results based on the interpretation of the year “81.”

Breaking down the Date Masks

The yy Date Mask

• Definition: The yy mask allows you to input a two-digit year.
• Interpretation: Oracle automatically assumes the two digits pertain to the current century.
  • For example, if the current year is 2023, a date like 01/05/81 would be interpreted as the year 2081.
• Use Case: This can lead to unexpected results, especially for dates referring to the 20th century.

The rr Date Mask

• Definition: The rr mask also accepts two-digit years but uses a different method for interpreting them.
• Interpretation: The two-digit years are rounded to determine the full year:
  • 50-99: Treated as years 1950-1999.
  • 00-49: Treated as years 2000-2049.
• Example: Thus, 01/05/81 would be interpreted as 1981, which is likely the intended reference in most cases.

Examples Recap

Here’s a recap of how these masks affect the output:

• The query using yy:
  • Assumes 01/05/81 is 2081 which most likely has no corresponding hires.
• The query using rr:
  • Sees the same date as 1981, which presumably has hire records in the emp table.

Conclusion

Understanding the differences between yy and rr is crucial when writing Oracle SQL queries involving dates. While yy can lead to misinterpretation of future years, the rr mask provides a more sensible approach to converting two-digit years with its rounding rules.

Choosing the right mask can save you from inadvertently excluding data and ensure that your SQL queries yield the intended results. For most cases regarding historical data, the rr mask is recommended for clarity and accuracy.

Always make sure to consider the context of your data and the current year when working with two-digit years to avoid pitfalls and errors in interpretation.