SQL 错误：ORA-01861：文字与格式字符串 01861 不匹配
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22542882/
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
SQL Error: ORA-01861: literal does not match format string 01861
提问by LizzyPooh
I am trying to insert data into an existing table and keep receiving an error.
我正在尝试将数据插入现有表并不断收到错误消息。
INSERT INTO Patient
(
PatientNo,
PatientFirstName,
PatientLastName,
PatientStreetAddress,
PatientTown,
PatientCounty,
PatientPostcode,
DOB,
Gender,
PatientHomeTelephoneNumber,
PatientMobileTelephoneNumber
)
VALUES
(
121,
'Miles',
'Malone',
'64 Zoo Lane',
'Clapham',
'United Kingdom',
'SW4 9LP',
'1989-12-09',
'M',
02086950291,
07498635200
);
Error:
错误：
Error starting at line : 1 in command -
INSERT INTO Patient (PatientNo,PatientFirstName,PatientLastName,PatientStreetAddress,PatientTown,PatientCounty,PatientPostcode,DOB,Gender,PatientHomeTelephoneNumber,PatientMobileTelephoneNumber)
VALUES (121, 'Miles', 'Malone', '64 Zoo Lane', 'Clapham', 'United Kingdom','SW4 9LP','1989-12-09','M',02086950291,07498635200)
Error report -
SQL Error: ORA-01861: literal does not match format string
01861. 00000 - "literal does not match format string"
*Cause: Literals in the input must be the same length as literals in
the format string (with the exception of leading whitespace). If the
"FX" modifier has been toggled on, the literal must match exactly,
with no extra whitespace.
*Action: Correct the format string to match the literal.
Just not sure why this keeps happening I am learning SQL at the moment, any help will be greatly appreciated!
只是不知道为什么这会一直发生我现在正在学习 SQL，任何帮助将不胜感激！
回答by mustaccio
Try replacing the string literal for date '1989-12-09'with TO_DATE('1989-12-09','YYYY-MM-DD')
尝试更换字符串字面日期'1989-12-09'与TO_DATE('1989-12-09','YYYY-MM-DD')
回答by grvsmth
You can also change the date format for the session. This is useful, for example, in Perl DBI, where the to_date() function is not available:
您还可以更改会话的日期格式。这很有用，例如，在 Perl DBI 中，to_date() 函数不可用：
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD'
You can permanently set the default nls_date_format as well:
您也可以永久设置默认的 nls_date_format：
ALTER SYSTEM SET NLS_DATE_FORMAT='YYYY-MM-DD'
In Perl DBI you can run these commands with the do() method:
在 Perl DBI 中，您可以使用 do() 方法运行这些命令：
$db->do("ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD');
http://www.dba-oracle.com/t_dbi_interface1.htmhttps://community.oracle.com/thread/682596?start=15&tstart=0
http://www.dba-oracle.com/t_dbi_interface1.htm https://community.oracle.com/thread/682596?start=15&tstart=0
回答by Mitz
The format you use for the date doesn't match to Oracle's default date format.
您用于日期的格式与 Oracle 的默认日期格式不匹配。
A default installation of Oracle Database sets the DEFAULT DATE FORMAT to dd-MMM-yyyy.
Oracle 数据库的默认安装将 DEFAULT DATE FORMAT 设置为dd-MMM-yyyy.
Either use the function TO_DATE(dateStr, formatStr)or simply use dd-MMM-yyyydate format model.
要么使用函数，TO_DATE(dateStr, formatStr)要么简单地使用dd-MMM-yyyy日期格式模型。
回答by Vibe
try to save date like this yyyy-mm-dd hh:mm:ss.ms for example: 1992-07-01 00:00:00.0 that worked for me
尝试像这样保存日期 yyyy-mm-dd hh:mm:ss.ms 例如：1992-07-01 00:00:00.0 对我有用
回答by Dulith De Costa
ORA-01861: literal does not match format string
This happens because you have tried to enter a literal with a format string, but the length of the format string was not the same length as the literal.
发生这种情况是因为您尝试输入带有格式字符串的文字，但格式字符串的长度与文字的长度不同。
You can overcome this issue by carrying out following alteration.
您可以通过执行以下更改来解决此问题。
TO_DATE('1989-12-09','YYYY-MM-DD')
As a general rule, if you are using the TO_DATE function, TO_TIMESTAMP function, TO_CHAR function, and similar functions, make sure that the literal that you provide matches the format string that you've specified
作为一般规则，如果您使用 TO_DATE 函数、TO_TIMESTAMP 函数、TO_CHAR 函数和类似函数，请确保您提供的文字与您指定的格式字符串匹配
回答by gopal
Try the format as dd-mon-yyyy, For example 02-08-2016 should be in the format '08-feb-2016'.
尝试使用 dd-mon-yyyy 格式，例如 02-08-2016 应采用“08-feb-2016”格式。
回答by KAMAL
If you provide proper date format it should work please recheck once if you have given correct date format in insert values
如果您提供了正确的日期格式，它应该可以工作，如果您在插入值中提供了正确的日期格式，请重新检查一次
