“无效月份"在 INSERT 语句上 [英] "Not a valid month" on an INSERT statement

问题描述

我有一个 sql 语句:

I have an sql statement:

Insert into MYSCHEMA.TABLE1 (ID,MY_DATE) values (167600,to_timestamp('15-APR-14 01.36.58.803000000 PM','DD-MON-RR HH.MI.SS.FF AM'));

当我读到这个格式是正确的(如此处) 但oracle 返回错误SQL Error: ORA-01843: not a valid month.知道如何解决这个问题吗?我使用 Oracle 数据库 11b 快捷版.

As I read this format is correct (as described here) but oracle returns an error SQL Error: ORA-01843: not a valid month. Any idea how to solve this problem? I use Oracle Database 11b Express Edition.

推荐答案

除非你在表上有一个触发器来设置日期或时间戳列，这会在完整的错误堆栈中给出一些指示，听起来你的 NLS_DATE_LANGUAGE 不需要英文月份缩写.

Unless you have a trigger on the table that is setting a date or timestamp column, which would give some indication in the full error stack, it sounds like your NLS_DATE_LANGUAGE is not expecting an English-language month abbreviation.

您所拥有的内容在英语中是有效的:

What you have is valid in English:

alter session set nls_timestamp_format = 'RR/MM/DD HH24:MI:SSXFF';
alter session set nls_date_language ='ENGLISH';

select to_timestamp('15-APR-14 01.36.58.803000000 PM',
  'DD-MON-RR HH.MI.SS.FF AM') as my_date
from dual;

MY_DATE                   
---------------------------
14/04/15 13:36:58.803000000 

但如果您会话的默认日期语言是波兰语(根据您的个人资料推测)，则会出现此错误 - 错误消息仍为英文:

But if your session's default date language is Polish (guessing from your profile) it will give this error - with the error message still in English:

alter session set nls_date_language ='POLISH';

select to_timestamp('15-APR-14 01.36.58.803000000 PM',
  'DD-MON-RR HH.MI.SS.FF AM') as my_date
from dual;

SQL Error: ORA-01843: not a valid month
01843. 00000 -  "not a valid month"

如果您不想将会话设置为英语，您可以通过提供 to_timestamp() 的可选第三个参数:

If you don't want to set your session to English you can override that for a specific statement by giving the optional third parameter to to_timestamp():

alter session set nls_date_language ='POLISH';

select to_timestamp('15-APR-14 01.36.58.803000000 PM',
  'DD-MON-RR HH.MI.SS.FF AM',
  'NLS_DATE_LANGUAGE=ENGLISH') as my_date
from dual;

MY_DATE                   
---------------------------
14/04/15 13:36:58.803000000 

您还可以通过使用月份数字代替月份名称或使用 ANSI 时间戳文字语法:

You can also avoid the issue entirely by using month numbers instead of month names, or using the ANSI timestamp literal syntax:

select timestamp '2014-04-15 13:36:58.803' from dual;

TIMESTAMP'2014-04-1513:36:58.803'
---------------------------------
14/04/15 13:36:58.803000000       

这些方法也都适用于日期列；to_date() 函数 以相同的方式受 NLS 设置的影响，并且具有相同的可选日期语言参数.

These methods also all work for date columns; the to_date() function is affected by NLS settings in the same way and has the same optional date language parameter.

这篇关于“无效月份"在 INSERT 语句上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！