转换不同世纪的日期 [英] Converting dates from different centuries

问题描述

我有一个登台表，其中包含日期作为字符串，格式为"mm/dd/yy".我有Oracle 11g过程将字符串转换为日期格式，然后再加载到主表中.我正在使用to_date('03/20/34','mm/dd/rr')转换为日期格式，该错误格式输出为03/20/2034，而正确的日期为03/20/1934.请帮我获得正确的输出，其中我的表中包含两个世纪以来的日期.

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.

推荐答案

我正在使用to_date('03/20/34'，'mm/dd/rr')转换为日期格式，这会产生错误的输出，显示为03/20/2034，而正确的日期是03/20 /1934."

"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是上个千年中引入的Oracle黑客，它是解决 Y2K斗争的一部分错误.标准日期掩码YY将世纪默认为当前世纪.但是在1999年，01/01/00更有可能是01/01/2000而不是01/01/1900.因此，RR破解是使用在00上旋转的固定窗口来得出日期的世纪:将值00-49赋予世纪20，将值50-99赋予19.显然，在某些时候，这种猜测是错误的，但是引入的数据损坏级别低于将所有日期默认为世纪19的级别.

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.

关键是，窗户是固定的.它之所以成为临时解决方案，是因为没有时间在2000年到来之前将所有旧系统切换为使用四位数的年份.但是，人们的愿景始终是，即使仅通过报废或更换，所有系统都将在长期内得到修复.当然，没有人期望会建立支持两位数年的新系统.

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.

现在是 2017 ，没有理由让系统仍使用两位数年份.过去，存储空间非常昂贵，而从日期中删除两位数可节省大量宝贵的空间.现在只是草率了.

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.

这显然不能帮助您解决问题.简短的答案是无法更改RR使用的枢轴.最好的解决方案是对系统的数据输入方面进行更严格的验证，并坚持四位数的年份.这是否可行取决于您的办公室政治.另一种解决方案是编写自己的转换函数:

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;

显然，您需要定义实际规则来决定使用19还是20.

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

这篇关于转换不同世纪的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！