oracle “DD-MON-RR”日期格式模式未按预期工作
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22398798/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
'DD-MON-RR' date format pattern not working as expected
提问by SarthAk
I am inserting a query with the format as
我正在插入格式为的查询
to_date('25-JUN-13','DD-MON-RR')
In oracleit is working fine and the output is as 25-JUN-13.
In postgresqlthe same is working as 0001-06-25 BC.
在oracle 中它工作正常，输出为25-JUN-13.
在postgresql 中，它与0001-06-25 BC.
It is a migration projectfrom the oracle database to postgresql. Any solution for the same to work as it is in the case of oracle.
是一个从oracle数据库到postgresql的迁移项目。任何解决方案都可以像在oracle的情况下一样工作。
The same is not working correctly if I am using the DD-MM-YYformat then the result is being very much different.
如果我使用DD-MM-YY格式，则同样无法正常工作，那么结果就会大不相同。
RUNNING THIS QUERY IN POSTGRESQL -->
在 POSTGRESQL 中运行此查询 -->
select to_char(to_date('25-JUN-53','DD-MON-YY'),'YYYY') as YEAR
ANSWER IS --> 2053
While retrieving the same result in oracle from the query as
在从查询中检索与 oracle 相同的结果时
select to_char(to_date('25-JUN-53','DD-MON-RR'),'YYYY') as YEAR from dual
ASSWER IS --> 1953
As I am migrating the project the same functionality should be there in the Postgresql so that the final result should be same.
当我迁移项目时，Postgresql 中应该有相同的功能，以便最终结果应该是相同的。
回答by Erwin Brandstetter
The same is not working correctly ...
同样不能正常工作......
Of course it is working correctly. Per documentation:
当然，它工作正常。根据文档：
If the year format specification is less than four digits, e.g.
YYY, and the supplied year is less than four digits, the year will be adjusted to be nearest to the year 2020, e.g.95becomes 1995.
如果年份格式规范小于四位数，例如
YYY，并且提供的年份小于四位数，则年份将调整为最接近2020 年，例如95变为 1995。
So, 70becomes 1970, but 69becomes 2069.
所以，70变成了 1970 年，但69变成了 2069 年。
Oracle has different rules for the format specifier RR(which does not exist in Postgres), basically the year will be adjusted to be nearest to the year 2000(the nearest century to the current date):
Oracle 对格式说明符有不同的规则RR（Postgres 中不存在），基本上年份会调整为最接近2000 年（距离当前日期最近的世纪）：
Workaround
解决方法
I would encapsulate the functionality in a function that switches the century according to the year number in the string. Since Postgres allows function overloading, you can even use the same function name to_date()with different parameter types.
我会将功能封装在一个函数中，该函数根据字符串中的年份编号切换世纪。由于 Postgres 允许函数重载，您甚至可以使用to_date()具有不同参数类型的相同函数名。
According to the documentation above, Oracle wraps around at YY = '50' and this function is equivalent until 2049:
根据上面的文档，Oracle 在 YY = '50' 处回绕，这个函数在 2049 年之前是等效的：
CREATE OR REPLACE FUNCTION to_date(varchar, text)
RETURNS date AS
$func$
SELECT CASE WHEN right(, 2) > '49' THEN
to_date(left(, -2) || '19' || right(, 2), 'DD-MON-YYYY')
ELSE
to_date(left(, -2) || '20' || right(, 2), 'DD-MON-YYYY')
END
$func$ LANGUAGE sql STABLE;
Only STABLE, not IMMUTABLE, because to_date is only STABLE. Else you disable function inlining.
只有STABLE，没有IMMUTABLE，因为 to_date 只是STABLE. 否则你禁用函数内联。
I chose varcharfor the first parameter to be different from the original, which uses text.
If the year number is > 49, the function adds the 20th century (with '19') else, the 21st into the date string before conversion. The second parameter is ignored.
我选择varchar第一个参数与原始参数不同，它使用text.
如果年份数 > 49，则该函数在转换前将 20 世纪（带有“19”）添加到日期字符串中，否则将 21 世纪添加到日期字符串中。第二个参数被忽略。
Call:
称呼：
SELECT to_date('25-JUN-53' , 'DD-MON-YY') AS original
, to_date('25-JUN-53'::varchar, 'DD-MON-YY') AS patched1
, to_date('25-JUN-53'::varchar, 'DD-MON-RR') AS patched2
, to_date('25-JUN-53'::varchar, 'FOO-BAR') AS patched3Our custom function ignores the 2nd parameter anyway.
我们的自定义函数无论如何都会忽略第二个参数。
Result:
结果：
original | patched
------------+-----------
2053-06-25 | 1953-06-25
You might make it more sophisticated to work beyond 2049 and take the second parameter into consideration ...
您可能会使其在 2049 年以后的工作更加复杂并考虑第二个参数......
A word of warning: function overloading over basic functions is better done with care. If that stays in your system somebody might get surprising results later.
一个警告：函数重载超过基本函数最好小心完成。如果它留在您的系统中，那么稍后可能会有人得到令人惊讶的结果。
Better create that function in a special schema and set the search_pathselectively so it only gets used when appropriate. You can as well use textas parameter type in this case:
最好在特殊模式中创建该函数并有search_path选择地设置它，以便仅在适当的时候使用它。text在这种情况下，您也可以使用参数类型：
CREATE SCHEMA specialfunc;
CREATE OR REPLACE FUNCTION specialfunc.to_date(text, text) AS ...
Then:
然后：
SET search_path = specialfunc, pg_catalog;
SELECT to_date('25-JUN-53', 'DD-MON-YY') AS patched;
Oruse a temporaryfunction. See:
或者使用临时函数。看：
回答by Amir Hossain
Here is a sample function I wrote to solve this issue. I myself was working on same migration. Hope it help u someway. You may do it using function overloading though, just rename the function as u like.
这是我为解决此问题而编写的示例函数。我自己也在做同样的迁移。希望它以某种方式帮助你。不过，您可以使用函数重载来实现，只需按照您的喜好重命名函数即可。
CREATE OR REPLACE FUNCTION to_date_rr(TEXT, TEXT)
RETURNS DATE AS
$$
DECLARE
date_v DATE;
fmt text := upper();
DATE_VALUE TEXT :=;
digit_diff numeric := length() - length();
BEGIN
= upper();
IF substring(fmt from position('RRRR' in fmt) for 4) = 'RRRR' THEN
IF digit_diff < 0 THEN
fmt := replace(, 'RRRR', 'YYYY');
IF substring(DATE_VALUE from position('RRRR' in ) for 2) > '50' THEN
date_v := to_date(overlay(DATE_VALUE placing '19' from position('RRRR' in ) for 0), fmt);
ELSE
date_v := to_date(overlay(DATE_VALUE placing '20' from position('RRRR' in ) for 0), fmt);
END IF;
ELSE
fmt := replace(, 'RRRR', 'YYYY');
date_v := to_date(, fmt);
END IF;
ELSIF substring(fmt from position('RR' in fmt) for 2) = 'RR' THEN
IF digit_diff = 0 THEN
fmt := replace(, 'RR', 'YY');
IF substring(DATE_VALUE from position('RR' in ) for 2) > '50' THEN
date_v := to_date(overlay(DATE_VALUE placing '19' from position('RR' in ) for 0), fmt);
ELSE
date_v := to_date(overlay(DATE_VALUE placing '20' from position('RR' in ) for 0), fmt);
END IF;
ELSE
fmt := replace(, 'RR', 'YY');
date_v := to_date(, fmt);
END IF;
ELSIF substring(fmt from position('YY' in fmt) for 2) = 'YY' and substring(fmt from position('YYYY' in fmt) for 4) != 'YYYY' THEN
IF digit_diff = 0 THEN
IF substring(DATE_VALUE from position('YY' in ) for 2) >= '00' THEN
date_v := to_date(overlay(DATE_VALUE placing '20' from position('YY' in ) for 0), fmt);
END IF;
ELSIF digit_diff < 0 THEN
IF substring(DATE_VALUE from position('YY' in ) for 2) >= '00' THEN
date_v := to_date(overlay(DATE_VALUE placing '200' from position('YY' in ) for 0), fmt);
END IF;
ELSE
date_v := to_date(, fmt);
END IF;
ELSE
SELECT to_date(, ) INTO date_v;
END IF;
RETURN date_v;
END;
$$
LANGUAGE plpgsql;
