oracle SQL 错误：ORA-01861：文字与格式字符串不匹配
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34501988/
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
提问by E_T_A
I keep getting this error in Oracle when i try to run this statement. I am not sure where the formatting error is coming from. maybe someone with fresh eyes can assist me with this problem.
当我尝试运行此语句时，我在 Oracle 中不断收到此错误。我不确定格式错误来自哪里。也许有新眼光的人可以帮助我解决这个问题。
INSERT INTO Faculty
(FacNo, FacFirstName, FacLastName, FacCity, FacState,
FacDept, FacRank, FacSalary, FacSupervisor, FacHireDate, FacZipCode)
VALUES ('543-21-0987','VICTORIA','EMMANUEL','BOTHELL','WA','MS','PROF',120000.0,'','2001-04-15','98011-2242');
Here is the error message i keep getting:
这是我不断收到的错误消息：
Error starting at line : 1 in command - 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.
从命令中的第 1 行开始出错 - 错误报告 - SQL 错误：ORA-01861：文字与格式字符串 01861 不匹配。00000 - “文字与格式字符串不匹配” *原因：输入中的文字长度必须与格式字符串中的文字（前导空格除外）。如果已打开“FX”修饰符，则文字必须完全匹配，没有多余的空格。*操作：更正格式字符串以匹配文字。
Here are the specs on the table i am trying to INSERT this data into:
以下是我试图将这些数据插入到表格中的规格：
FACNO CHAR(11 BYTE)
FACFIRSTNAME VARCHAR2(30 BYTE)
FACLASTNAME VARCHAR2(30 BYTE)
FACCITY VARCHAR2(30 BYTE)
FACSTATE CHAR(2 BYTE)
FACZIPCODE CHAR(10 BYTE)
FACRANK CHAR(4 BYTE)
FACHIREDATE DATE
FACSALARY NUMBER(10,2)
FACSUPERVISOR CHAR(11 BYTE)
FACDEPT CHAR(6 BYTE)
FACNO CHAR(11 BYTE)
FACFIRSTNAME VARCHAR2(30 BYTE)
FACLASTNAME VARCHAR2(30 BYTE)
FACCITY VARCHAR2(30 BYTE)
FACSTATE CHAR(2 BYTE)
FACZIPCODE CHAR(10 BYTE)
FACRANK CHAR(4
BYATE
20 FAC ) FACRANK CHAR(4 BYATE 20 FAC ) )
FACSUPERVISOR CHAR(11 BYTE)
FACDEPT CHAR(6 BYTE)
回答by OldProgrammer
Most likely, your NLS_DATE_FORMAT, the default date format for literals does not match your string. Never assume dates are formatted one way or another. use TO_DATEfunction to specify the format, so convert to :
最有可能的是，您NLS_DATE_FORMAT的文本的默认日期格式与您的字符串不匹配。永远不要假设日期的格式是这样或那样的。使用TO_DATE函数指定格式，因此转换为：
Insert (... to_date('2001-04-15','YYYY-MM-DD')...
Insert (... to_date('2001-04-15','YYYY-MM-DD')...
回答by Evan Steinbrenner
OldProgrammers answer is the correct answer. Explicitly converting the string to a date is the safest. MS-SQL will generally automatically convert anything recognizable as a date and Oracle will do it if your formatting matches the default formatting for the system. All the oracle systems I have worked with used 'DD/MON/YY' or the two digit day, the three letter month abbreviation and the two digit year as their default and would automatically convert that. Not the proper way to do it but everybody likes to be lazy sometimes.
OldProgrammers 的答案是正确的答案。将字符串显式转换为日期是最安全的。MS-SQL 通常会自动转换任何可识别为日期的内容，如果您的格式与系统的默认格式匹配，Oracle 会执行此操作。我使用过的所有 oracle 系统都使用“DD/MON/YY”或两位数的日期、三个字母的月份缩写和两位数的年份作为默认值，并且会自动转换。这不是正确的方法，但每个人有时都喜欢偷懒。
回答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('2001-04-15','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 函数和类似函数，请确保您提供的文字与您指定的格式字符串匹配
