SQL ORA-01861: 文字与格式字符串不匹配
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1387917/
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
ORA-01861: literal does not match format string
提问by
When I try to execute this snippet:
当我尝试执行此代码段时：
cmd.CommandText = "SELECT alarm_id,definition_description,element_id,
TO_CHAR (alarm_datetime, 'YYYY-MM-DD HH24:MI:SS'),severity,
problem_text,status FROM aircom.alarms
WHERE status = 1 and
TO_DATE (alarm_datetime,'DD.MM.YYYY HH24:MI:SS') > TO_DATE ('07.09.2008
09:43:00', 'DD.MM.YYYY HH24:MI:SS')
order
by ALARM_DATETIME desc";
I get:
我得到：
ORA-01861: literal does not match format string
There is no problem with database connection because I can execute basic SQL commands.
数据库连接没有问题，因为我可以执行基本的SQL命令。
What is the problem with this statement?
这个说法有什么问题？
回答by Christian13467
Remove the TO_DATE in the WHERE clause
删除 WHERE 子句中的 TO_DATE
TO_DATE (alarm_datetime,'DD.MM.YYYY HH24:MI:SS')
and change the code to
并将代码更改为
alarm_datetime
The error comes from to_date conversion of a date column.
错误来自日期列的 to_date 转换。
Added Explanation:Oracle converts your alarm_datetime into a string using its nls depended date format. After this it calls to_date with your provided date mask. This throws the exception.
添加说明：Oracle 使用其依赖于 nls 的日期格式将您的 alarm_datetime 转换为字符串。在此之后，它会使用您提供的日期掩码调用 to_date。这会引发异常。
回答by OMG Ponies
The error means that you tried to enter a literal with a format string, but the length of the format string was not the same length as the literal.
该错误意味着您尝试输入带有格式字符串的文字，但格式字符串的长度与文字的长度不同。
One of these formats is incorrect:
其中一种格式不正确：
TO_CHAR(t.alarm_datetime, 'YYYY-MM-DD HH24:MI:SS')
TO_DATE(alarm_datetime, 'DD.MM.YYYY HH24:MI:SS')
回答by sweta
SELECT alarm_id
,definition_description
,element_id
,TO_CHAR (alarm_datetime, 'YYYY-MM-DD HH24:MI:SS')
,severity
, problem_text
,status
FROM aircom.alarms
WHERE status = 1
AND TO_char (alarm_datetime,'DD.MM.YYYY HH24:MI:SS') > TO_DATE ('07.09.2008 09:43:00', 'DD.MM.YYYY HH24:MI:SS')
ORDER BY ALARM_DATETIME DESC
回答by Vijay
Just before executing the query: alter session set NLS_DATE_FORMAT = "DD.MM.YYYY HH24:MI:SS"; or whichever format you are giving the information to the date function. This should fix the ORA error
就在执行查询之前：alter session set NLS_DATE_FORMAT = "DD.MM.YYYY HH24:MI:SS"; 或您向日期函数提供信息的任何格式。这应该可以修复 ORA 错误
