Oracle Database: Convert Dates to Before 2000 for Years 50-99 and to 2000 and Later for Years 00-49

Oracle Database: Convert Dates to Before 2000 for Years 50-99 and to 2000 and Later for Years 00-49

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • SQL
  • 2 mins read

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: