Difference between RR and yy for date format characters

Recently there is an application, connected to the library of 11g, its own logic is roughly based on the date and other conditions to delete the corresponding history of the table, where the date condition is judged not to use to_date (), where the date and other conditions are a composite primary key, and then insert a new record (the Date field here uses To_date ( XX, ' Ddmonyy '). In the test, the tester found a problem, first manually inserted a 2050-year record, and then execute the application, found the primary key conflict, and then traced because the original record is not deleted, causing the new inserted record primary key conflict, looks very strange problem, why did not delete the old record?

The reason for this is that the date in the deleted search condition uses the Ddmonyy date format character, but when it is inserted without any date format, the default date format of the database is used, which is:

SELECT * FROM nls_database_parameters
WHERE parameter=‘NLS_DATE_FORMAT‘;

The default value for Nls_date_format after 9i is DD-MON-RR. Ddmon month and day formatted characters are the same, the formatted characters for the year are used two-bit, RR and yy, then the question now is Ddmonyy and DDMONRR in the judgment of the year what is the difference?

First, look at DD-MON-RR, the following is a description of the official documentation:

The RR Datetime Format Element

The RR DateTime format element is similar to the YY datetime format element, but it provides additional flexibility for St Oring date values in the other centuries. The RR DateTime format element lets you store 20th century dates on the 21st century by specifying only the last of the digit S of the year.

RR date format is similar to YY date format, but for different centuries, he provides additional extensibility for storing date values. The RR date format allows you to store 20th century dates in 21st century by specifying only the latter two digits of the year.

If You use the To_date function with the YY datetime format element and then the year returned always have the same first 2 di Gits as the current year. If you use the RR for datetime format element instead, then the century of the return value varies according to the specified Two-digit year and the last of the digits of the year.

If you use the YY format character in the To_date function, only the first two-digit annual fee numbers that are the same as the current year are returned. If you use the RR format, you can return different values based on the specified two-digit year number, and the following two digits of the current year.

That is:

• If the specified two-digit is xx to

  • If the last of both digits of the current year is, then the returned year has the same first and digits as the Curre NT year.

  • If the last of both digits of the current year is to be the first 2 digits of the returned year is 1 greater than t He first 2 digits of the current year.

• If The specified two-digit is a

  • If the last of both digits of the current year is, then the first 2 digits of the returned year is 1 less than the First 2 digits of the current year.
  • If the last of both digits of the current year is to be in the returned year have the same first and digits as the Curre NT year.

If the specified two-digit year number is 00-49, then

1. If the last two digits of the current year are 00-49, the returned year is the same as the first two digits of the current year.
2. If the last two digits of the current year are 50-99, the first two digits of the returned year are 1 larger than the first two digits of the current year.

If the specified two-digit year number is 50-99, then

1. If the last two digits of the current year are 00-49, the first two digits of the returned year are 1 smaller than the first two digits of the current year.
2. If the last two digits of the current year are 50-99, the returned year is the same as the first two digits of the current year.

The following example:

1. Assume that the following query occurs between 1950-1999:

   Select To_char (to_date (' 27-oct-98 ', ' dd-mon-rr '), ' YYYY ') "year" from DUAL;

   1998

   Select To_char (to_date (' 27-oct-17 ', ' dd-mon-rr '), ' YYYY ') "year" from DUAL;

   2017

2. Assume that the following query occurs between 2000-2049:

   Select To_char (to_date (' 27-oct-98 ', ' dd-mon-rr '), ' YYYY ') "year" from DUAL;

   1998

   Select To_char (to_date (' 27-oct-17 ', ' dd-mon-rr '), ' YYYY ') "year" from DUAL;

   2017

The query returns the same value regardless of whether the SQL was executed before or after 2000. SQL that is written using the RR date format can return the same value based on the first two digits of a different year.

Next look at Ddmonyy, provided by the two-digit year number, this time for the century will be the same as the current database server settings for the century, for example, now is 2015, using to_date (' 01jan50 ', ' Ddmonyy '), then the deposit is 2050-01-01.

Looking back at the questions that began,
1. The tester manually inserts a 2050-year record.
2. The application first performs the delete operation, at this time the date condition does not use To_date (), directly uses ' 01jan50 ', the default is the DDMONRR format character, is currently 2015, therefore the actual deletion condition is the 1950 record. The current table does not have a record of 1950 years, so the number of deleted record bars is 0.
3. The application performs the insert operation, at which time the date query condition is ' 01jan50 ', which is currently 2015, so the actual record to be inserted is 2015. However, because a 2050-year record already exists in the table, the primary key violation error is reported.

Summing up, the most direct way is to use YYYY or RRRR full-year representation, so there is no misunderstanding, if the use of YY or RR two digits for the year, it is necessary to understand the difference between the two, and their own needs, after all, Oracle also according to the rules to determine the two-bit years of which century, therefore need to choose the appropriate way, and when a transaction has multiple additions and deletions to date conditions of logic, then the use of YY or RR to be consistent, otherwise, due to inconsistent conditions, resulting in different results, a small date format, containing a different understanding, showing the design of the Oracle system is exquisite, Also to the user to make certain requirements, the principle is more important than the usage.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Difference between RR and yy for date format characters