fnd_date.canonical_to_date 函数在 oracle 中不起作用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26086963/
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
fnd_date.canonical_to_date function not working in oracle
提问by user3809240
I have used fnd_date.canonical_to_dateto convert a column to date data type.
我曾经fnd_date.canonical_to_date将一列转换为日期数据类型。
the expected output is for example:29-JAN-2014
but i am getting : 29-JAN-0014
anyone knows why ?
有谁知道为什么？
l_actual_term_date varchar2(100);
SELECT to_char(ACTUAL,'DD-MON-RRRR')
INTO l_actual_term_date
FROM per_table;
--assign to another variable as per reqmt of date type
prev_term_date :=l_actual_term_date;
--WHEN printed this will give 29-JAN-12
--then in another package have to aasign prev_term_date to a varchar type variable
P_prev_term_date :=fnd_date.canonical_to_date(prev_term_date);
--when i print this P_prev_term_date i get - 29-JAN-0014 instead of 29-JAN-2014
回答by Erich Kitzmueller
prev_term_dateis probably a date variable, and your default date format is dd.mon.yy. What happens when you call fnd_date.canonical_to_date(prev_term_date)is an implicit conversion of prev_term_dateto VARCHAR2, using the default date format dd.mon.yy; fnd_date.canonical_to_datethen uses a date format of dd.mon.yyyyto convert it back to date, therefore misinterpreting the year.
prev_term_date可能是一个日期变量，您的默认日期格式是dd.mon.yy. 调用时会发生什么，使用默认日期格式fnd_date.canonical_to_date(prev_term_date)隐式转换prev_term_date为 VARCHAR2 dd.mon.yy；fnd_date.canonical_to_date然后使用日期格式dd.mon.yyyy将其转换回日期，因此误解了年份。
回答by Adam
If ACTUAL column is a datetype then use:
如果 ACTUAL 列是日期类型，则使用：
SELECT ACTUAL INTO prev_term_date FROM per_table;
If you need a string that represents datein canonical format do:
如果您需要以规范格式表示日期的字符串，请执行以下操作：
l_actual_term_date := fnd_date.date_to_canonical(prev_term_date);
I assume that prev_term_dateis a datetype.
我假设这prev_term_date是一个日期类型。
