Check if current date is between two dates Oracle SQL

Question

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 1

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

Solution 2

SELECT to_char(emp_login_date,'DD-MON-YYYY HH24:MI:SS'),A.* 
FROM emp_log A
WHERE emp_login_date BETWEEN to_date(to_char('21-MAY-2015 11:50:14'),'DD-MON-YYYY HH24:MI:SS')
AND
to_date(to_char('22-MAY-2015 17:56:52'),'DD-MON-YYYY HH24:MI:SS') 
ORDER BY emp_login_date

Comments

[paul]

do you need to TO_DATE() sysdate? Surely it is already a date

[Avinesh Kumar]

thanks paul, According the Gordon Linoffs solution below sysdate is already a date.

[Jeffrey Kemp]

You don't need SQL for this - in PL/SQL you can just do something like if sysdate between date '2014-02-28' and date '2014-06-21' then v := 1; end if;

[Avinesh Kumar]

thanks Jeffrey Kemp this also works fine for me.

[Nathan Tuggy]

Generally, answers are much more helpful if they include an explanation of what the code is intended to do, and why that solves the problem without introducing others. (This post was flagged by at least one user, presumably because they thought an answer without explanation should be deleted.)

[anilech]

trunc(sysdate) in this example (dual table) is ok to handle time portion of a date, but in the real table with indexed DATE field it may prevent Oracle from using the corresponding index. I would suggest not using "between" clause but use comparison operators here: where DATE_FIELD >= TO_DATE('28/02/2014', 'DD/MM/YYYY') and DATE_FIELD < TO_DATE('20/06/2014', 'DD/MM/YYYY') + 1

[Abhishek]

This works like charm. Just need to understand formatting with to_date.

[Aba]

Anoter way to deal with the index is from dba-oracle.com/t_sql_display_all_days_between_two_dates.htm: select ... where trunc(ORDER_DATE) between ... BEWARE: Using the trunc function will invalidate the index on order_date, and you may want to create a temporary function-based index on trunc(order_date)

[Gordon Linoff]

@Aba . . . Am I missing something? There is no order_date in this question and no index on dual.

[Aba]

@GordonLinoff I was just commenting on the issue of trunc() and indexes which anilech brought to light when, as he pointed out, when dealing with a real table.