Oracle NLS_DATE_FORMAT无法正常工作 [英] Oracle NLS_DATE_FORMAT not working properly
问题描述
在我的应用程序中，我正在设置会话的NLS_DATE_FORMAT以使用以下命令设置程序返回的所有日期的格式:
In my application I am setting the NLS_DATE_FORMAT of the session to format all dates returned by the program using the following command:
alter session set nls_date_format='DY DDTH MON YYYY';
这应该返回如下日期:"2013年8月23日星期五"
This should return the date like this: 'Fri 23rd Aug 2013'
但是，如果我运行以下查询:
However, if I run the following query:
select SYSDATE from dual;
我以以下格式获取日期:"FRI 23 AUG 2013".请注意，在"23"之后缺少"rd".
I get the date in the following format: 'FRI 23 AUG 2013'. Notice the lack of the 'rd' after '23'.
如果我运行以下查询:
select to_char(sysdate, 'DY DDTH MON YYYY') from dual;
日期确实以所需的格式返回.那么，通过NLS_DATE_FORMAT设置"TH"日期格式参数时为什么不起作用?还有什么其他日期格式参数在使用TO_CHAR而不通过NLS_DATE_FORMAT时有效吗?
The date does come back in the desired format. So why does the 'TH' date format parameter not work when setting it through NLS_DATE_FORMAT? And are there any other date format parameters that do work when using TO_CHAR but not through NLS_DATE_FORMAT?
感谢您的帮助
顺便说一句，我正在使用Oracle 11.2客户端和数据库.
By the way, I am using Oracle 11.2 client and database.
我在SQL Developer中运行alter session命令.我在SQL PLUS中尝试了此操作，它以正确的格式返回了日期.但是，现在我在插入格式化日期时遇到了问题.
I was running the alter session command from within SQL Developer. I tried this in SQL PLUS and it returned the date in the correct format. However, now I am having a problem with inserting formatted dates.
如果执行以下操作(如上设置NLS_DATE_FORMAT之后)，我将收到"ORA-01861文字与格式字符串不匹配"错误:
If I do the following (after setting the NLS_DATE_FORMAT as above) I get a 'ORA-01861 literal does not match format string' error:
INSERT INTO DATE_TABLE (MY_DATE_COL) VALUES ('Thu 18th Apr 2013');
我希望设置NLS_DATE_FORMAT后可以正常工作吗?
I would expect that to work after setting the NLS_DATE_FORMAT?
如果我将插入语句更改为:
If I change the insert statement to:
INSERT INTO DATE_TABLE (MY_DATE_COL) VALUES ('Thu 18 Apr 2013');
然后它起作用了！为什么会这样?
Then it does work! Why is this?
推荐答案
如果您这样做:
alter session set nls_date_format='DD DDTH MON YYYY';
您收到错误ORA-01810: format code appears twice.
如果您使用与TO_CHAR相同的格式模型，那么它将起作用:
If you use the same format model as your TO_CHAR then it works:
alter session set nls_date_format='DY DDTH MON YYYY';
Session altered.
select SYSDATE from dual;
SYSDATE
-----------------
FRI 23RD AUG 2013
这适用于SQL Developer和SQL * Plus.
This works in SQL Developer and SQL*Plus.
对于有关插入的更新问题，请日期时间格式文档说:
For your updated question about inserting, the datetime format documentation says:
有关日期格式元素后缀的注释:
-
将这些后缀之一添加到datetime格式元素时，返回值始终为英文.
When you add one of these suffixes to a datetime format element, the return value is always in English.
Datetime后缀仅对格式化输出有效.您不能使用它们在数据库中插入日期.
Datetime suffixes are valid only to format output. You cannot use them to insert a date into the database.
因此，不能显式地或通过NLS_DATE_FORMAT将带有后缀的字符串用作插入的一部分.您必须将其从字符串中删除，或定制格式模型以将其视为固定值.
So you cannot use a string with the suffix as part of your insert, either explicitly or via the NLS_DATE_FORMAT. You would have to remove it from the string or tailor the format model to treat is as a fixed value.
这篇关于Oracle NLS_DATE_FORMAT无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
