How to filter based on the date in Oracle SQL

oracle, sql

Solution

First, try this below, it works:

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

Then, you need to convert the right side of the inequalities to dates.

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')

If you want to write it a bit cleaner, use `BETWEEN` instead:

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')

EDIT:

Here's a complete example:

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')

Output:

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

Problem

I had a query as below in my function. In that I need to filter based on the 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') ``` In My DB a.submit_date had a value as '14-06-2012 20:30' But its throwing Invalid month... can anyone help to correct this?

Original source