Suppose you need to migrate historical data from Oracle or from other databases, which also can be in a text format. And in the date columns, you have dates like '970522', '001101', or '95/11/01' which are in YYMMDD
format and could be in any format. Now there is a need to import this data into Oracle database table having date fields, and you need to convert the dates to before 2000 for years 50-99 and to 2000 and later for years 00-49. This issue is referred to be a Y2K problem.
To handle this situation in Oracle, you can use RR
format element for Year with Oracle's TO_DATE()
function. The following are the examples:
Convert Dates to Before 2000 for Years 50-99
Example-1
select to_date('500111', 'RRMMDD') from dual;
Output
01/11/1950
Example-2
select to_date('971201', 'RRMMDD') from dual;
Output
12/01/1997
Example-3
select to_char(to_date('25/12/99', 'DD/MM/RR'), 'DD/Mon/RR') from dual;
Output
25/Dec/99
Convert Dates to 2000 and Later for Years 00-49
Example-1
select to_date('000101', 'RRMMDD') from dual;
Output
01/01/2000
Example-2
select to_char(to_date('31jan10', 'DDMONRR'), 'DD-MON-RRRR') from dual;
Output
31-JAN-2010
Example-3
select to_date('010149', 'DDMMRR') from dual;
Output
01/01/2049
Hope this will help you to handle the Y2K issue for dates in Oracle.
See also:
- Oracle Date Functions | List of Quick Examples
- Oracle TO_DATE Function Examples
- Get Current Local Time of Any Country in PL/SQL