SQL ORA-01847 月份中的某一天必须介于 1 和月份的最后一天之间 - 但数据正常
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22439654/
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-01847 day of month must be between 1 and last day of month - but data is OK
提问by Joggl
Problem is solved - see end of this post.
问题已解决 - 请参阅本文末尾。
when i call to_date in select clause everything works fine - get a resultset of 12 records:
当我在 select 子句中调用 to_date 时一切正常 - 得到 12 条记录的结果集：
select value1,to_date(value1,'DD.MM.YYYY')
from variableindex
where
value1 is not null
and value1 <> '0'
and creation_time_ > to_timestamp('20140307','YYYYMMDD')
order by 2
returns
回报
'VALUE1' 'TO_DATE(VALUE1,'DD.MM.YYYY')'
'25.11.2013' 25.11.13
'12.03.2014' 12.03.14
'12.03.2014' 12.03.14
'12.03.2014' 12.03.14
'12.03.2014' 12.03.14
'12.03.2014' 12.03.14
'14.03.2014' 14.03.14
'14.03.2014' 14.03.14
'14.03.2014' 14.03.14
'14.03.2014' 14.03.14
'20.03.2014' 20.03.14
'20.03.2014' 20.03.14
Every datestring has been converted as expected.
每个日期字符串都已按预期转换。
If i add the following line to where clause
如果我将以下行添加到 where 子句
and to_date(value1,'DD.MM.YYYY') < to_date('20140301','YYYYMMDD')
i'll receive:
我会收到：
ORA-01847: Tag des Monats muss zwischen 1 und letztem Tag des Monats liegen
01847. 00000 - "day of month must be between 1 and last day of month"
*Cause:
*Action:
No it really gets nasty... i changed my query to
不，它真的很讨厌……我将查询更改为
where id_ in (...)
and used the same 12 recordsets ids as in original query. No Error...
并使用与原始查询中相同的 12 个记录集 ID。没有错误...
Many thanks to @GordonLinoff - this is how i use the query now:
非常感谢@GordonLinoff - 这就是我现在使用查询的方式：
select value1,to_date(value1,'DD.MM.YYYY') from variableindex
where
(case when value1 <> '0' then to_date(value1,'DD.MM.YYYY') end) > to_timestamp('20131114','YYYYMMDD')
and creation_time_ > to_timestamp('20140307','YYYYMMDD')
order by 2;
采纳答案by Gordon Linoff
This is your query with the whereclause:
这是您对where子句的查询：
select value1, to_date(value1,'DD.MM.YYYY')
from variableindex
where value1 is not null and
value1 <> '0' and
creation_time_ > to_timestamp('20140307', 'YYYYMMDD') and
to_date(value1 'DD.MM.YYYY') < to_date('20140301', 'YYYYMMDD')
order by 2;
Oracle does not guarantee the order of processing of clauses in the where. So, value <> '0'is not guaranteed to run before the last condition. This happens to be a big problem on SQL Server. One solution is to use a casestatement:
Oracle 不保证where. 所以，value <> '0'不能保证在最后一个条件之前运行。这恰好是 SQL Server 上的一个大问题。一种解决方案是使用case语句：
select value1,to_date(value1, 'DD.MM.YYYY')
from variableindex
where value1 is not null and
value1 <> '0' and
creation_time_ > to_timestamp('20140307', 'YYYYMMDD') and
(case when value <> '0' then to_date(value1, 'DD.MM.YYYY') end) <
to_date('20140301', 'YYYYMMDD')
order by 2;
Rather ugly, but it just might solve your problem.
相当丑陋，但它可能会解决您的问题。
回答by yu yang Jian
If you're using OracleParameterin SQL with parameter name and value binding, check you set the oracleCommand.BindByName = truethen it'll bind by name, and not by parameter adding order.
如果您OracleParameter在 SQL 中使用参数名称和值绑定，请检查您设置了oracleCommand.BindByName = true然后它将按名称绑定，而不是按参数添加顺序。
