查询:
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.
任何想法如何解决?
谢谢ar
使用标准日期语法和显式 join
s:
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
和ISO 8601标准日期格式,则不必担心日期常数的本地化设置。您也不需要用功能调用来弄乱您的查询。
首先,您必须遵循Gordon建议,以使用标准日期语法和显式连接。其次,从您发布的查询中,t6.EI_Q17
列的varchar
似乎具有date
。在这种情况下,您需要将其施放两次,如下所示:
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