Oracle NLS_DATE_FORMAT 无法正常工作

2021-12-30 00:00:00 date oracle11g oracle

在我的应用程序中,我正在设置会话的 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.

相关文章