ORA-01843:条件更改的月份无效

Question

我对以下错误有问题

ORA-01843:无效月份

如果我使用

SELECT  mx.work_order_no, mx.work_order_name, mx.comments
  FROM  max_orders mx
 WHERE  TO_DATE (wo_dt, 'dd/mm/rr') <= (TO_DATE (SYSDATE, 'dd/mon/rr') - 7)

但是，如果我将where条件子句更改为

WHERE   TO_DATE (wo_dt, 'dd/mm/rr') >= (TO_DATE (SYSDATE, 'dd/mon/rr') - 7)

我对此有异议

ORA-01843:无效月份

是什么导致了这种情况，以及如何解决此错误？

更新1

底层视图

SELECT    work_order_no,
          work_order_name,
                 wo_dt,
                comments
      FROM    (SELECT     mx_master.work_order_no,
                             mx_master.work_order_name,
                             SUBSTR (mx_master.uom, 1, 15) wo_dt,
                             mx_master.remarks
                    FROM     mx_wo_data mx_master)

Answer 1

我建议您创建一个存储函数来标识坏行：

create function invalid_date(p_d in varchar2) return number as
  v_d date;
begin
  v_d := TO_DATE(p_d, 'dd/mm/rr');
  return 0;
exception 
  when others then
    return 1;
end;
/

select * from mx_orders where invalid_date(wo_dt)=1;

Answer 2

SYSDATE已经是一个约会了。您不应该将它传递到TO_DATE()。当您这样做时，您正在执行对字符串的隐式转换，以及显式的返回转换。戈登·林诺夫已经展示了一种更好的方法。

根据您添加的视图定义，wo_dt是一个字符串。您期望它是dd/mm/rr格式的。错误是告诉您该列中有哪些值实际上不是该格式的，因此您需要检查视图或基础表中的数据，以查看哪些记录有不正确的数据。

您可以使用something like this来排除格式不正确的值；或者更有用地识别坏值，以便可以删除或纠正这些值，例如：

select * from max_orders
where my_to_date(wo_dt, 'dd/mm/rr') is null;

或从下表：

select * from mx_wo_data
where my_to_date(substr(uom, 1, 8), 'dd/mm/rr') is null;

如果不能创建函数，则可以在匿名块中使用相同的逻辑。

奇怪的是，更改条件会导致错误，因为(隐式和显式)转换是在计算条件之前应用的，而使用函数意味着不能使用该列上的任何索引；因此(在没有任何其他筛选器的情况下)您应该对这两个查询进行全表扫描，在对其进行筛选之前，应该将转换应用到列中的所有值，并且应该以任何方式获得错误。所以这并不能真正回答这个问题的那个方面。

Answer 3

不要把sysdate转换成约会！只需使用：

WHERE TO_DATE(wo_dt, 'dd/mm/rr') >= trunc(SYSDATE - 7)