I have an sql statement:
我有一条sql语句:
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'));
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.
正如我读到的这种格式是正确的(如本文所述),但是oracle返回一个错误的SQL错误:ORA-01843:不是一个有效的月份。你知道怎么解决这个问题吗?我使用Oracle数据库11b Express Edition。
1 个解决方案
#1
6
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.
除非您的表上有一个正在设置日期或时间戳列的触发器,该触发器将在完整的错误堆栈中给出一些指示,否则您的NLS_DATE_LANGUAGE不希望使用英语月份缩写。
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"
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()
:
如果您不想将会话设置为英语,您可以通过为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
You can also avoid the issue entirely by using month numbers instead of month names, or using the ANSI timestamp literal syntax:
您还可以使用月号而不是月名来完全避免这个问题,或者使用ANSI时间戳字面语法:
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
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.
这些方法也适用于日期列;to_date()函数也受NLS设置的影响,具有相同的可选日期语言参数。
#1
6
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.
除非您的表上有一个正在设置日期或时间戳列的触发器,该触发器将在完整的错误堆栈中给出一些指示,否则您的NLS_DATE_LANGUAGE不希望使用英语月份缩写。
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"
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()
:
如果您不想将会话设置为英语,您可以通过为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
You can also avoid the issue entirely by using month numbers instead of month names, or using the ANSI timestamp literal syntax:
您还可以使用月号而不是月名来完全避免这个问题,或者使用ANSI时间戳字面语法:
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
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.
这些方法也适用于日期列;to_date()函数也受NLS设置的影响,具有相同的可选日期语言参数。