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
and49
is entered, this will be stored as a "next century" year. For example,02
entered in 1996 will be stored as2002
. - If the current year is in the second half of the century (50 - 99), and a two-digit year between
50
and99
is entered, this will be stored as a "current century" year. For example,97
entered in 1996 will be stored as1997
. - If the current year is in the first half of the century (00 - 49), and a two-digit year between
00
and49
is entered, this will be stored as a "current century" year. For example,02
entered in 2001 will be stored as2002
. - If the current year is in the first half of the century (00 - 49), and a two-digit year between
50
and99
is entered, this will be stored as a "previous century" year. For example,97
entered in 2001 will be stored as1997
.
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/10Reference :
hire_date
----------------
01-jan-2003
01-jan-1967
http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/b10795/adfns_ty.htm