sqloracle-databasedatebetweensysdate

Check if current date is between two dates Oracle SQL


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

Solution

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