ORACLE RR DATES说明(转)

The RR date format element of the TO_DATE and TO_CHAR functions allows a database site to default the century to different values depending on the two-digit year, so that years 50 to 99 default to 19xx and years 00 to 49 default to 20xx. Therefore, regardless of the current century at the time the data is entered, the RR format will ensure that the year stored in the database is as follows:

• If the current year is in the second half of the century (50 - 99), and a two-digit year between 00 and 49 is entered, this will be stored as a "next century" year. For example, 02 entered in 1996 will be stored as 2002.
• If the current year is in the second half of the century (50 - 99), and a two-digit year between 50 and 99 is entered, this will be stored as a "current century" year. For example, 97 entered in 1996 will be stored as 1997.
• If the current year is in the first half of the century (00 - 49), and a two-digit year between 00 and 49 is entered, this will be stored as a "current century" year. For example, 02 entered in 2001 will be stored as 2002.
• If the current year is in the first half of the century (00 - 49), and a two-digit year between 50 and 99 is entered, this will be stored as a "previous century" year. For example, 97 entered in 2001 will be stored as 1997.

The RR date format is available for inserting and updating DATE data in the database. It is not required for retrieval or query of data already stored in the database as Oracle Database has always stored the YEAR component of a date in its four-digit form.

Here is an example of the RR usage:

INSERT INTO employees (employee_id, department_id, hire_date) VALUES    (9999, 20, TO_DATE('01-jan-03', 'DD-MON-RR')); INSERT INTO employees (employee_id, department_id, hire_date) VALUES     (8888, 20, TO_DATE('01-jan-67',  'DD-MON-RR')); SELECT employee_id, department_id,    TO_CHAR(hire_date, 'DD-MON-YYYY') hire_date FROM employees;
current system date :2001/10/10 hire_date ---------------- 01-jan-2003 01-jan-1967

current system date :2051/10/10 hire_date ---------------- 01-JAN-2103 01-JAN-2067

current system date :1906/10/10 hire_date ---------------- 01-jan-1903 01-jan-1867 *this is a theory of value!

current system date :1996/10/10 hire_date ---------------- 01-jan-2003 01-jan-1967