Check if current date is between two dates Oracle SQL

Avinesh Kumar picture Avinesh Kumar · Apr 30, 2014 · Viewed 279.5k times · Source

I would like to select 1 if current date falls between 2 dates through Oracle SQL.

I wrote an SQL after reading through other questions.

https://stackoverflow.com/questions/2369222/oracle-date-between-query

https://stackoverflow.com/questions/2399753/select-from-table-by-knowing-only-date-without-time-oracle

But it returned only null. sysdate is the current date that is 01/05/2014 in date format DD/MM/YYYY.

The SQL I wrote is:

select 1 from dual 
WHERE to_date(sysdate,'DD/MM/YYYY') 
BETWEEN TO_DATE('28/02/2014', 'DD/MM/YYYY')
AND TO_DATE('20/06/2014', 'DD/MM/YYYY');

and

select 1 from dual 
WHERE to_date(sysdate,'DD/MM/YYYY') >= TO_DATE('28/02/2014', 'DD/MM/YYYY') 
AND to_date(sysdate,'DD/MM/YYYY') < TO_DATE('20/06/2014', 'DD/MM/YYYY'); 

Answer

Gordon Linoff picture Gordon Linoff · May 1, 2014

You don't need to apply to_date() to sysdate. It is already there:

select 1
from dual 
WHERE sysdate BETWEEN TO_DATE('28/02/2014', 'DD/MM/YYYY') AND TO_DATE('20/06/2014', 'DD/MM/YYYY');

If you are concerned about the time component on the date, then use trunc():

select 1
from dual 
WHERE trunc(sysdate) BETWEEN TO_DATE('28/02/2014', 'DD/MM/YYYY') AND
                             TO_DATE('20/06/2014', 'DD/MM/YYYY');