Oracle 插入时间戳 - 需要 AM/AM 或 PM/PM
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20700694/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle insert timestamp - AM/A.M. or PM/P.M. required
提问by Mato.Duris
In SQL Developer I have created an export, .sql file, (a really big one). It contains hundreds of inserts like :
在 SQL Developer 中，我创建了一个导出 .sql 文件（一个非常大的文件）。它包含数百个插入，如：
"Insert into SCHEMA.TABLE (
... ,
CREATEDATE,
MODIFIEDDATE,
....
)
values (
... ,
to_timestamp('20-AUG-12 01.09.53.271000000 AM','DD-MON-RR HH.MI.SS.FF AM'),
to_timestamp('20-AUG-12 01.09.53.271000000 PM','DD-MON-RR HH.MI.SS.FF PM'),
...
);"
When I run it in SQL Developer, it works fine. But I can't import a bit .sql file with SQL Developer (or I don't know how to do it).
当我在 SQL Developer 中运行它时，它工作正常。但是我无法使用 SQL Developer 导入一点 .sql 文件（或者我不知道该怎么做）。
When I run it with SQL*Plus I get following error:
当我使用 SQL*Plus 运行它时，出现以下错误：
ORA-01855: AM/A.M. or PM/P.M. required
ORA-01855：需要 AM/AM 或 PM/PM
When I remove the AM/PM keywords from the insert, SQL*Plus can insert row, with them, no luck. What is the problem with AM / PM?
当我从插入中删除 AM/PM 关键字时，SQL*Plus 可以插入行，但没有运气。AM / PM 有什么问题？
Here is the exact insert command:
这是确切的插入命令：
Insert into SCHEMA.TABLE(
ENTRYID,GROUPID,COMPANYID,USERID,USERNAME,CREATEDATE,MODIFIEDDATE,
CLASSNAMEID,CLASSPK,CLASSUUID,VISIBLE,STARTDATE,ENDDATE,PUBLISHDATE,
EXPIRATIONDATE,MIMETYPE,TITLE,DESCRIPTION,SUMMARY,URL,HEIGHT,WIDTH,
PRIORITY,VIEWCOUNT
) values (
11902,11005,10136,10178,'Test Test',
to_timestamp('20-AUG-12 01.09.53.271000000 AM','DD-MON-RR HH.MI.SS.FF AM'),
to_timestamp('20-AUG-12 01.09.53.271000000 PM','DD-MON-RR HH.MI.SS.FF PM'),
10076,11900,'AAA',1,null,null,null,null,null,
'ABC','XyZ',null,null,0,0,0,2563
);
回答by Alex Poole
The only immediate way I can see to reproduce this is to change the NLS setings; but not NLS_LANGUAGEas that would affect the error message as well. Using English is OK as you'd expect:
我可以看到重现此问题的唯一直接方法是更改 NLS 设置；但不会NLS_LANGUAGE因为这也会影响错误消息。如您所料，使用英语是可以的：
SQL> alter session set NLS_DATE_LANGUAGE='ENGLISH';
Session altered.
SQL> select value from nls_session_parameters
2 where parameter = 'NLS_DATE_LANGUAGE';
VALUE
--------------------------------------------------------------------------------
ENGLISH
SQL> insert into t42 (createddate)
2 values (to_timestamp('20-08-12 01.09.53.271000000 AM','DD-MM-RR HH.MI.SS.FF PM'));
1 row created.
Changing just the NLS_DATE_LANGUAGEreproduces your error:
仅更改会NLS_DATE_LANGUAGE重现您的错误：
alter session set NLS_DATE_LANGUAGE='SLOVAK';
Session altered.
SQL> insert into t42 (createddate)
2 values (to_timestamp('20-AUG-12 01.09.53.271000000 AM','DD-MON-RR HH.MI.SS.FF PM'));
values (to_timestamp('20-AUG-12 01.09.53.271000000 AM','DD-MON-RR HH.MI.SS.FF PM'))
*
ERROR at line 2:
ORA-01855: AM/A.M. or PM/P.M. required
Changing NLS_LANGUAGE, directly or through something that influences it, would make the error message appear differently as well:
NLS_LANGUAGE直接或通过影响它的东西更改，也会使错误消息显示不同：
SQL> alter session set NLS_LANGUAGE = 'SLOVAK';
Relácia zmenená.
SQL> insert into t42 (createddate)
2 values (to_timestamp('20-AUG-12 01.09.53.271000000 AM','DD-MON-RR HH.MI.SS.FF PM'));
values (to_timestamp('20-AUG-12 01.09.53.271000000 AM','DD-MON-RR HH.MI.SS.FF PM'))
*
ERROR v riadku 2:
ORA-01855: vy?aduje sa AM/A.M. alebo PM/P.M.
You'd need a language that still recognises AUGas a valid month, but doesn't use AM/PM; Slovak seems to fit the bill, so I'd guess that might be where you're from. Other languages may do the same too. Bit strange that the error message when NLS_LANGUAGEis still set shows AM/PM, to but to_char(sysdate, 'HH AM')indicates that message is wrong. With only NLS_DATE_LANGUAGEset the message is in English anyway (try it with Turkish, for example, but you'd have to change the month name or switch use month numbers) and it always shows AM/PM.
您需要一种仍可识别AUG为有效月份但不使用AM/ 的语言PM；斯洛伐克语似乎符合要求，所以我猜这可能是您来自哪里。其他语言也可以这样做。有点奇怪的NLS_LANGUAGE是，仍然设置时的错误消息显示 AM/PM，但to_char(sysdate, 'HH AM')表示该消息是错误的。只NLS_DATE_LANGUAGE设置消息无论如何都是英文的（例如，尝试使用土耳其语，但您必须更改月份名称或切换使用月份数字）并且它始终显示 AM/PM。
If that is what's happening, either change the NLS_DATE_LANGUAGEto English before running this script; or change your SQL Developer settings to use Slovak (or whatever you actually use) before doing the export - which would hopefully make the export use DOPOLUDNIE/POPOLUDNIE, though it wouldn't entirely surprise me if it didn't; or change the SQL Developer NLS_TIMESTAMP_FORMATto use 24-hour time (and a 4-digit year, and month numbers instead of names) for the export, which would avoid the issue entirely. Or run the script from SQL Developer, of course.
如果发生这种情况，请NLS_DATE_LANGUAGE在运行此脚本之前将 更改为英语；或者在导出之前更改您的 SQL Developer 设置以使用斯洛伐克语（或您实际使用的任何内容） - 这有望使导出使用DOPOLUDNIE/ POPOLUDNIE，尽管如果不这样做，我也不会感到惊讶；或者将 SQL Developer 更改NLS_TIMESTAMP_FORMAT为使用 24 小时制（以及 4 位年份和月份数字而不是名称）进行导出，这将完全避免该问题。当然，或者从 SQL Developer 运行脚本。
回答by Ben
Alex's answer describes the problem very well; there is a simpler solution though, if you're able to change your generated insert statements slightly. TO_TIMESTAMP()has a third parameter, which lets you specify the NLS_DATE_LANGUAGE in the insert itself.
Alex 的回答很好地描述了这个问题；不过，如果您能够稍微更改生成的插入语句，则有一个更简单的解决方案。TO_TIMESTAMP()有第三个参数，它允许您在插入本身中指定 NLS_DATE_LANGUAGE。
create table a ( tstamp timestamp );
Table created.
insert into a
values (to_timestamp( '20-08-12 01.09.53.271000000 AM'
, 'DD-MM-RR HH.MI.SS.FF PM'
, 'nls_date_language = ENGLISH'));
1 row created.
select *
from a;
TSTAMP
------------------------------------------------------
20-AUG-12 01.09.53.271000
You don't then need to alter the date language for the session, or alter it back afterwards.
然后，您无需更改会话的日期语言，或之后将其更改回来。
回答by MT0
Without any data to look at I can't be sure but it sound like your "big SQL file" does not always (or sometimes) have the time stamps in the format you are expecting and is not using AMor PM.
没有任何数据可供查看，我无法确定，但听起来您的“大 SQL 文件”并不总是（或有时）具有您期望的格式的时间戳，并且没有使用AMor PM。
Try using a 24-hour clock rather than AMor PMand see if that matches the data in the "big SQL file":
尝试使用 24 小时制而不是AM或PM并查看是否与“大 SQL 文件”中的数据匹配：
...
to_timestamp('20-AUG-12 01.09.53.271000000','DD-MON-RR HH24.MI.SS.FF'),
to_timestamp('20-AUG-12 13.09.53.271000000','DD-MON-RR HH24.MI.SS.FF'),
...
