Re ORA-01843:不是有效的月份错误

Question

查询：

SELECT t3.e_id, t6.ei_id
FROM t3, t6
WHERE 
(TO_CHAR(t6.EI_Q17, 'YYYYMMDD') BETWEEN 20160801 AND 20170731)
AND (t3.E_STATUS                = 'W')
AND (t3.E_START                 < '1/8/2016')
AND (t3.E_END                   > '31/7/2017')

但是我得到一个错误：ORA-01843: not a valid month.

你知道如何解决这个问题吗？

谢谢，阿尔

Answer 1

使用标准日期语法和显式join：

SELECT t3.e_id, t6.ei_id
FROM t3 CROSS JOIN t6
WHERE t6.EI_Q17 BETWEEN DATE '2016-08-01' AND DATE '2017-07-31' AND
      t3.E_STATUS = 'W' AND
      t3.E_START < DATE '2016-08-01' AND
      t3.E_END > DATE '2017-07-31';

如果使用DATE和ISO8601标准日期格式，则不必担心日期常量的本地化设置。你也不需要用函数调用来搞乱你的查询。

Answer 2

首先，您必须遵循戈登的建议来使用标准日期语法和显式连接。其次，从您发布的查询看，列t6.EI_Q17是具有date的varchar。在这种情况下，您需要强制转换两次，如下所示：

SELECT t3.e_id, t6.ei_id
  FROM t3 CROSS JOIN t6
 WHERE     TO_CHAR (TO_DATE (t6.EI_Q17, 'YYYY-MM-DD'), 'YYYYMMDD') BETWEEN 20160801
                                                                       AND 20170731
       AND t3.E_STATUS = 'W'
       AND t3.E_START < TO_DATE ('2016-08-01', 'YYYY-MM-DD')
       AND t3.E_END > TO_DATE ('2017-07-31', 'YYYY-MM-DD')

简单的演示：

SQL> select dt 
    from
    (select to_char(to_date('2017-06-01','YYYY-MM-DD'),'YYYYMMDD') dt from dual )
    where dt between 20170601 and 20170603 ;

输出：

20170601