ORA-01861: 文字与格式字符串不匹配
当我尝试执行此代码片段时：
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";
我得到：
ORA-01861: literal does not match format string
数据库连接没有问题，因为我可以执行基本的 SQL 命令。
这个说法有什么问题呢？
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.
What is the problem with this statement?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
删除 WHERE 子句中的 TO_DATE
并将代码更改为
错误来自于日期列的 to_date 转换。
添加说明：Oracle 使用其 nls 相关日期格式将 Alarm_datetime 转换为字符串。之后，它会使用您提供的日期掩码调用 to_date 。这会引发异常。
Remove the TO_DATE in the WHERE clause
and change the code to
The error comes from to_date conversion of a date column.
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.
该错误意味着您尝试输入带有格式字符串的文字，但格式字符串的长度与文字的长度不同。
以下格式之一不正确：
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:
在执行查询之前：
更改会话集 NLS_DATE_FORMAT = "DD.MM.YYYY HH24:MI:SS";
或您向日期函数提供信息的任何格式。这应该可以修复 ORA 错误
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
从实体框架执行时，像这样的简单视图给了我 ORA-01861 错误：
只需执行类似的操作即可修复它：
我认为问题是 EF 日期配置与 Oracle 的配置不同。
A simple view like this was giving me the ORA-01861 error when executed from Entity Framework:
Just did something like this to fix it:
I think the problem is EF date configuration is not the same as Oracle's.
如果您使用 JPA 进行休眠，请确保实体具有针对日期列定义的字段的正确数据类型，例如使用 java.util.Date 而不是 String。
If you are using JPA to hibernate make sure the Entity has the correct data type for a field defined against a date column like use java.util.Date instead of String.