Oracle NLS_DATE_FORMAT 无法正常工作
在我的应用程序中，我正在设置会话的 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';
这应该返回这样的日期:'Fri 23rd Aug 2013'
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:格式代码出现两次.
如果您使用与 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:
关于日期格式元素后缀的说明:
当您将这些后缀之一添加到日期时间格式元素时，返回值始终为英文.
When you add one of these suffixes to a datetime format element, the return value is always in English.
日期时间后缀仅对格式化输出有效.您不能使用它们将日期插入到数据库中.
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.
相关文章