遇到SQL错误:ORA-01843:无效的月份 [英] Encountering SQL Error: ORA-01843: not a valid month

问题描述

我使用此查询创建了一个表
CREATE TABLE Store (id number(11) primary key not null, opening_time timestamp CHECK (EXTRACT(HOUR FROM opening_time) > 8 || NULL));

现在，当我尝试使用insert into Store values(1, '04/04/2012 13:35 PM');插入一些数据时，遇到此错误SQL Error: ORA-01843: not a valid month.

我做错了什么事?

I created a table using this query
CREATE TABLE Store (id number(11) primary key not null, opening_time timestamp CHECK (EXTRACT(HOUR FROM opening_time) > 8 || NULL));

Now, when I try to insert some data using insert into Store values(1, '04/04/2012 13:35 PM');, I encounter this error SQL Error: ORA-01843: not a valid month.

What is it that I am doing wrong ?

推荐答案

'04/04/2012 13:35 PM'不是日期-它是一个字符串.

'04/04/2012 13:35 PM' is not a date - it is a string.

当使用NLS_DATE_FORMAT会话参数的值作为格式掩码(注意:这是一个会话参数)将它们插入到DATE列中时，Oracle将对非日期文字进行隐式TO_DATE( string_value, format_mask ).并且属于客户；这不是全局设置).如果非日期文字匹配此格式，那么它将起作用(如果不匹配，则它将不起作用)-但是，如果NLS_DATE_FORMAT曾经被更改，那么它将立即中断(调试起来非常痛苦)因为正在运行的代码不会，但是没有人会更改代码.

Oracle will do an implicit TO_DATE( string_value, format_mask ) on non-date literals when inserting them into a DATE column using the value of the NLS_DATE_FORMAT session parameter as the format mask (note: this is a session parameter and belongs to the client; it is not a global setting). If the non-date literal matches this format then it will work (and if it doesn't then it won't) - however, if the NLS_DATE_FORMAT is ever changed then it will immediately break (any be a huge pain to debug as the code that was working won't but no-one will have changed the code).

您可以通过查询找到当前的NLS_DATE_FORMAT:

You can find out your current NLS_DATE_FORMAT with the query:

SELECT VALUE
FROM   NLS_SESSION_PARAMETERS
WHERE  PARAMETER = 'NLS_DATE_FORMAT';

最好以正确的格式掩码显式使用TO_DATE()或使用ANSI/ISO日期文字(即DATE '2012-04-04'或TIMESTAMP '2012-04-04 13:35').

It is better to explicitly use TO_DATE() with the correct format mask or to use an ANSI/ISO date literal (i.e. DATE '2012-04-04' or TIMESTAMP '2012-04-04 13:35').

您可以这样做:

INSERT INTO STORE ( id, opening_time )
  VALUES( 1, TO_DATE( '04/04/2012 13:35', 'DD/MM/YYYY HH24:MI' );

(您不需要AM/PM，因为小时部分已经在24小时制上)

(you do not need the AM/PM as the hour component is already on a 24 hour clock)

或

INSERT INTO STORE ( id, opening_time )
  VALUES( 1, TIMESTAMP '2012-04-04 13:35:00' );

(使用Oracle将隐式转换为日期的ANSI/ISO时间戳文字)

这篇关于遇到SQL错误:ORA-01843:无效的月份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！