sql - 如何根据 Oracle SQL 中的日期进行过滤

Question

我的函数中有如下查询。因为我需要根据 a.submit_date 进行过滤

SELECT mail_id , mail_async, mail_priority FROM glms_mail_sys_mail_store a
WHERE                  
--(TO_DATE(a.submit_date,'dd-MM-yyyy HH:mm') >= '14-06-2012 18:30')
--AND (TO_DATE(a.submit_date,'dd-MM-yyyy HH:mm') <= '15-06-2012 18:50')

(TO_DATE(TO_CHAR(a.submit_date,'dd-MM-yyyy HH:mm'),'dd-MM-yyyy HH:mm') >= '14-06-2012 18:30')
AND (TO_DATE(TO_CHAR(a.submit_date,'dd-MM-yyyy HH:mm'),'dd-MM-yyyy HH:mm') <= '15-06-2012 18:30')

在我的数据库中 a.submit_date 的值为 '14-06-2012 20:30'

但是它抛出 Invalid month...任何人都可以帮助纠正这个问题吗？

Answer 1

首先，试试下面这个，它有效:

SELECT TO_DATE('14-06-2012 20:30','dd-MM-yyyy HH24:MI') FROM dual

然后，您需要将不等式的右边转换为日期。

a.submit_date >= TO_DATE('14-06-2012 18:30','dd-MM-yyyy HH24:MI') AND
a.submit_date <= TO_DATE('15-06-2012 18:50','dd-MM-yyyy HH24:MI')

如果你想写得更干净一点，请改用BETWEEN:

a.submit_date BETWEEN
  TO_DATE('14-06-2012 18:30','dd-MM-yyyy HH24:MI') AND
  TO_DATE('15-06-2012 18:50','dd-MM-yyyy HH24:MI')

编辑:

这是一个完整的例子:

CREATE TABLE foo (submit_date DATE);

INSERT INTO foo VALUES (to_date('14-06-2012 20:30','dd-MM-yyyy HH24:MI'));
INSERT INTO foo VALUES (to_date('14-07-2012 20:30','dd-MM-yyyy HH24:MI'));

SELECT * FROM foo a
WHERE
a.submit_date BETWEEN
  TO_DATE('14-06-2012 18:30','dd-MM-yyyy HH24:MI') AND
  TO_DATE('15-06-2012 18:50','dd-MM-yyyy HH24:MI')

输出:

    SUBMIT_DATE
1   6/14/2012 8:30:00 PM