Oracle 日期格式图片在转换整个输入字符串之前结束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17206160/
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 date format picture ends before converting entire input string
提问by Buras
My table has two DATE format attributes, however, when i try to insert value it throws an error: date format picture ends before converting entire input string.Here is my attempted code:
我的表有两个 DATE 格式属性，但是，当我尝试插入值时，它抛出一个错误：日期格式图片在转换整个输入字符串之前结束。这是我尝试的代码：
insert into visit
values(123456, '19-JUN-13', '13-AUG-13 12:56 A.M.');
I think the problem is with 12:56but Oracle documentation says dateimplies both date and time.
我认为问题出在，12:56但 Oracle 文档说date暗示了日期和时间。
回答by ntalbs
Perhaps you should check NLS_DATE_FORMATand use the date string conforming the format.
Or you can use to_datefunction within the INSERTstatement, like the following:
也许您应该检查NLS_DATE_FORMAT并使用符合格式的日期字符串。或者您可以to_date在INSERT语句中使用函数，如下所示：
insert into visit
values(123456,
to_date('19-JUN-13', 'dd-mon-yy'),
to_date('13-AUG-13 12:56 A.M.', 'dd-mon-yyyy hh:mi A.M.'));
Additionally, Oracle DATEstores dateand timeinformation together.
此外，OracleDATE将日期和时间信息存储在一起。
回答by afeef
you need to alter session
你需要改变会话
you can try before insert
你可以在插入前尝试
sql : alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'
回答by Andrew Logvinov
What you're trying to insert is not a date, I think, but a string. You need to use to_date()function, like this:
我想您要插入的不是日期，而是字符串。您需要使用to_date()函数，如下所示：
insert into table t1 (id, date_field) values (1, to_date('20.06.2013', 'dd.mm.yyyy'));
回答by birwin
I had this error today and discovered it was an incorrectly-formatted year...
我今天遇到了这个错误，发现它是一个格式不正确的年份......
select * from es_timeexpense where parsedate > to_date('12/3/2018', 'MM/dd/yyy')
Notice the year has only three 'y's. It should have 4.
请注意，这一年只有三个 'y'。它应该有4个。
Double-check your format.
仔细检查您的格式。
