CODEWITHSUNDEEP
oracle-databasedateoracle11g
kraj
kraj

Reputation: 21

Converting dates from different centuries

I have staging table which contains date as string with format 'mm/dd/yy'. I have Oracle 11g procedure to convert the string to date format before loading into main table. I'm using to_date('03/20/34','mm/dd/rr') to convert into date format which is giving wrong output as 03/20/2034 whereas the correct date is 03/20/1934. Please help me out to get the correct output where my table contains dates from both centuries.

Upvotes: 2

Views: 2994

Answers (2)

Noah Schubert
Noah Schubert

Reputation: 1

I also encountered an issue like this, when inserting date values from the late 90s. The format in the script I was given read DD-MON-YY, so the database read that as 20YY, instead of 19YY.

My very inelegant solution was to open the raw data file and simply add a "19" before the YY year values.

Upvotes: 0

APC
APC

Reputation: 146229

"I'm using to_date('03/20/34','mm/dd/rr') to convert into date format which is giving wrong output as 03/20/2034 whereas the correct date is 03/20/1934. "

RR was a hack Oracle introduced in the last Millennium as part of the fight to resolve the Y2K bug. The standard date mask YY defaults the century to the current century. But in 1999 it was more likely that 01/01/00 meant 01/01/2000 rather than 01/01/1900. So the RR hack was to derive the century for dates using fixed windows pivoting on 00: values 00-49 are given century 20, 50-99 are given 19. Clearly some of the time this guess would be wrong, but the data corruption introduced was of a lower level than defaulting all dates to century 19.

The key point is, the windows are fixed. It was intended to be a temporary solution, because there wasn't time to switch all the legacy systems to use four-digit years before 2000 arrived. But the vision was always that all systems would be fixed in the long term, even if only through retirement or replacement. Certainly nobody expected that new systems would be built supporting two-digit years.

It is now 2017 and there is no excuse for systems to still be using two-digit years. Back in the old days storage was expensive, and shaving two digits from a date was a valuable space saving. Now it is just sloppiness.

Which obviously doesn't help you solve your problem. The short answer is there is no way to change the pivot used by RR. The best solution would be to enforce stricter validation on the data input aspect of your system, and insist on four-digit years. Whether that's feasible depends on your office politics. The other solution is to write your own conversion function:

 create or replace function my_to_date (p_str varchar2) return date as
 begin
     if to_number(substr(p_str, 7) <= 35 then
         return to_date(substr(p_str, 1, 6)||'19'||substr(p_str, 7), 'dd/mm/yyyy');
     else
         return to_date(substr(p_str, 1, 6)||'20'||substr(p_str, 7), 'dd/mm/yyyy');
 end;

Obviously you'll need to define the actual rules for deciding whether to use 19 or 20.

Upvotes: 2

Related Questions