sql - ORA-01839 "date not valid for month specified"对于 where 子句中的 to_date

Question

我有以下查询(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中可能有不正确的数据，所以执行以下查询:

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 .此外，以下查询执行时没有任何错误:

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

我已经尝试将 trunc() 添加到所有 to_date() 但没有成功。我还创建了 pl/sql 过程，它采用一项一项的形式 VIEW_BASE_MARIX_T 并将其转换为日期 - 一切正常。知道为什么我在第一次查询时出错吗？

UPD:在 View 中使用的表上的查询工作正常，但在 View 中 - 不是

UPD2:我们几乎没有相同产品的环境，但只有一个出现错误

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

Answer 1

评论有点太长了 - 创建一个简单的函数来测试日期:

CREATE FUNCTION is_Valid_Date(
  in_string VARCHAR2,
  in_format VARCHAR2 DEFAULT 'YYYY-MM-DD'
) RETURN NUMBER DETERMINISTIC
AS
  dt DATE;
BEGIN
  dt := TO_DATE( in_string, in_format );
  RETURN 1;
EXCEPTION
  WHEN OTHERS THEN
    RETURN 0;
END;
/

然后你可以这样做:

SELECT BOCRTNTIME
FROM   VIEW_BASE_MARIX_T
WHERE  is_Valid_Date( substr(BOCRTNTIME,1,10) ) = 0;

您可能会发现 4 月、6 月、9 月或 11 月有该月 31 日的条目，或者 2 月的值大于 28/29(尽管我在您粘贴的数据中看不到类似的内容)。

否则你可以尝试使用 ANSI 日期文字:

SELECT BOCRTNTIME
FROM   VIEW_BASE_MARIX_T
WHERE  to_date(substr(BOCRTNTIME,1,10),'YYYY-MM-DD') between DATE '2016-01-01' and DATE '2016-02-01';

或者，更简单，给定输入格式:

SELECT BOCRTNTIME
FROM   VIEW_BASE_MARIX_T
WHERE  substr(BOCRTNTIME,1,10) between '2016-01-01' and '2016-02-01';