SQL 错误: ORA-01861: 文字不匹配格式字符串01861

我正在尝试将数据插入到一个现有的表中，并不断收到一个错误。

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 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.

只是不知道为什么这种情况一直发生，我现在正在学习 SQL，任何帮助将非常感谢！

564999 次浏览
小开
最佳答案

Try replacing the string literal for date '1989-12-09' with TO_DATE('1989-12-09','YYYY-MM-DD')

小开

The format you use for the date doesn't match to Oracle's default date format.

A default installation of Oracle Database sets the DEFAULT DATE FORMAT to dd-MMM-yyyy.

Either use the function TO_DATE(dateStr, formatStr) or simply use dd-MMM-yyyy date format model.

小开

Try the format as dd-mon-yyyy, For example 02-08-2016 should be in the format '08-feb-2016'.

小开

If you provide proper date format it should work please recheck once if you have given correct date format in insert values

小开

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:

ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD'

You can permanently set the default nls_date_format as well:

ALTER SYSTEM SET NLS_DATE_FORMAT='YYYY-MM-DD'

In Perl DBI you can run these commands with the do() method:

$db->do("ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD');

http://www.dba-oracle.com/t_dbi_interface1.htm https://community.oracle.com/thread/682596?start=15&tstart=0

小开

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

小开 
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


提交答案