ORA-01839“日期对指定的月份无效"对于where子句中的to_date [英] ORA-01839 "date not valid for month specified" for to_date in where clause

问题描述

我有以下查询(BOCRTNTIME - varchar e.g 2015-02-28 12:21:45, VIEW_BASE_MARIX_T - some view):

select BOCRTNTIME
    from VIEW_BASE_MARIX_T
    where to_date(substr(BOCRTNTIME,1,10),'YYYY-MM-DD')
        between (to_date ('2016-01-01', 'YYYY-MM-DD'))
            and (to_date ('2016-02-01', 'YYYY-MM-DD'))

执行时出现错误:

ORA-01839:  "date not valid for month specified"

我认为BOCRTNTIME中的数据可能不正确，因此请执行以下查询:

I thought that there are can be incorrect data in BOCRTNTIME, so execute following query:

select distinct
         substr(BOCRTNTIME,1,8),
         substr(BOCRTNTIME,9,2)
  from VIEW_BASE_MARIX_T
 order by substr(BOCRTNTIME,9,2);

但是一切看起来都很好: http://pastebin.com/fNjP4UAu . 同样，以下查询将执行而没有任何错误:

But everything looks fine: http://pastebin.com/fNjP4UAu. Also following query executes without any error:

select to_date(substr(BOCRTNTIME,1,10),'YYYY-MM-DD') 
  from VIEW_BASE_MARIX_T;

我已经尝试将trunc()添加到所有to_date()，但是没有运气.我还创建了一个pl/sql过程，它以VIEW_BASE_MARIX_T形式一个接一个地将其转换为日期-一切正常. 有什么想法为什么我在第一次查询时会出错?

I already tried add trunc() to all to_date() but no luck. Also I create pl/sql procedure that takes one by one item form VIEW_BASE_MARIX_T and convert it to date - and everything works just fine. Any ideas why I get error on first query?

UPD:对在视图中使用的表的查询工作正常，但在视图中工作-不是

UPD: Query on table that used in view works fine, but in view - not

UPD2:我们在相同产品上的环境很少，但只能在一个产品上得到错误

UPD2: We have few enviroments with same products, but get error only on one

UPD3:通过在视图中使用的表中搜索无效日期解决了问题

UPD3: Issue was resolved by search non valid dates in table that used in view

推荐答案

我认为可能正在发生的事情是Oracle正在将谓词推送到视图的基础表.

I think that what might be happening is that Oracle is pushing the predicate to the underlying tables of the view.

您是否尝试运行查询

select to_date(substr(BOCRTNTIME,1,10),'YYYY-MM-DD') BOCRTNTIME
from MY_TABLE

而不是查询视图?

您还可以通过使用NO_PUSH_PRED提示来确认这一点

you can also confirm this by using the NO_PUSH_PRED hint

select /*+ NO_PUSH_PRED(VIEW_BASE_MARIX_T) */
BOCRTNTIME
from VIEW_BASE_MARIX_T
where 
to_date(substr(BOCRTNTIME,1,10),'YYYY-MM-DD') between (to_date ('2016-01-01', 'YYYY-MM-DD')) and (to_date ('2016-02-01', 'YYYY-MM-DD'))

这篇关于ORA-01839“日期对指定的月份无效"对于where子句中的to_date的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！