Oracle 日期 - SQL plus - 更改 INSERT 语句中使用的日期格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20848603/
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 dates - SQL plus - Changing the date format used in INSERT statements
提问by ziggy
I am trying to run a script that has the following insert statement through sqlplus
我正在尝试通过 sqlplus 运行具有以下插入语句的脚本
INSERT INTO mytable
VALUES('423234','test',NULL,'05-DEC-2012 18:30:03.000000');
INSERT INTO mytable
VALUES('2343423','test',NULL,'05-DEC-2012 18:30:03.000000');
INSERT INTO mytable
VALUES('234234','test',NULL,'05-DEC-2012 18:30:03.000000');
INSERT INTO mytable
VALUES('234234','test',NULL,'05-DEC-2012 18:30:03.000000');
INSERT INTO mytable
VALUES('2342343','test',NULL,'05-DEC-2012 18:30:04.000000');
When i run the script i get the following error:
当我运行脚本时，出现以下错误：
ERROR at line 2:
ORA-01849: hour must be between 1 and 12
I thought this was just an issue with the NLS_DATEFORMAT parameter and all i had to do was change it for the session and it would work. The problem is i cant get the correct format.
我认为这只是 NLS_DATEFORMAT 参数的问题，我所要做的就是为会话更改它，它会起作用。问题是我无法获得正确的格式。
Here is what i tried but i get a date format not recognized error
这是我尝试过的但我收到日期格式无法识别的错误
alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS.FF';
Isn't the above pattern correct for date '05-DEC-2012 18:30:04.000000'
上面的模式对日期不正确吗 '05-DEC-2012 18:30:04.000000'
By the way the script runs fine on SQLDeveloper.
顺便说一下，脚本在 SQLDeveloper 上运行良好。
Thanks
谢谢
回答by Alex Poole
You were close, but you need the TIMESTAMPequivalent:
你很接近，但你需要TIMESTAMP等效的：
alter session set NLS_TIMESTAMP_FORMAT='DD-MON-YYYY HH24:MI:SS.FF';
It would generally be better to not rely on the NLS settings and implicit conversions, and instead do an explicit conversion:
通常最好不要依赖 NLS 设置和隐式转换，而是进行显式转换：
INSERT INTO mytable
VALUES('2342343','test',NULL,
TO_TIMESTAMP('05-DEC-2012 18:30:04.000000', 'DD-MON-YYYY HH24:MI:SS.FF');
It's better to explicitly list the columns too, to allow for future changes and to make it easier to check you have the values in the right order.
最好也明确列出列，以允许将来进行更改并更容易检查您是否按正确顺序检查值。
