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

do you need to `TO_DATE()` sysdate? Surely it is already a date — paul, Apr 30 '14 at 21:55
thanks [paul](http://stackoverflow.com/users/1280587/paul), According the [Gordon Linoff](http://stackoverflow.com/users/1144035/gordon-linoff)s solution below `sysdate` is already a date. — Avinesh Kumar, May 01 '14 at 00:22
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;` — Jeffrey Kemp, May 01 '14 at 04:46
thanks [Jeffrey Kemp](http://stackoverflow.com/users/103295/jeffrey-kemp) this also works fine for me. — Avinesh Kumar, May 01 '14 at 08:28

score 91 · Accepted Answer · answered Apr 30 '14 at 22:00

91

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

answered Apr 30 '14 at 22:00

Gordon Linoff

1,242,037
58
646
786

3

`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` – anilech Jun 27 '18 at 14:34
Anoter way to deal with the index is from http://www.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)` – Aba Sep 20 '19 at 13:34
@Aba . . . Am I missing something? There is no `order_date` in this question and no index on dual. – Gordon Linoff Sep 20 '19 at 14:02
@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. – Aba Sep 20 '19 at 18:04

score 3 · Answer 2 · edited May 23 '15 at 15:31

3

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

edited May 23 '15 at 15:31

dario

5,149
12
28
32

answered May 23 '15 at 15:12

arun

61
1

3

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.) – Nathan Tuggy May 24 '15 at 01:29
This works like charm. Just need to understand formatting with to_date. – Abhishek Aug 24 '18 at 13:55

score 0 · Answer 3 · edited Aug 21 '18 at 15:55

TSQL: Dates- need to look for gaps in dates between Two Date

select
distinct
e1.enddate,
e3.startdate,
DATEDIFF(DAY,e1.enddate,e3.startdate)-1 as [Datediff]
from #temp e1 
   join #temp e3 on e1.enddate < e3.startdate          
       /* Finds the next start Time */
and e3.startdate = (select min(startdate) from #temp e5
where e5.startdate > e1.enddate)
and not exists (select *  /* Eliminates e1 rows if it is overlapped */
from #temp e5 
where e5.startdate < e1.enddate and e5.enddate > e1.enddate);

score 0 · Answer 4 · edited Jul 26 '23 at 11:53

0

In oracle use below,

select * from table_name where date_column_name between to_date('1-OCT-22') and ('31-OCT-22')

PS: replace table name and date column name and format accordingly

edited Jul 26 '23 at 11:53

Vinoth Krishnan

2,925
6
29
34

answered Nov 17 '22 at 07:02

Ashish Gupta

105
1
5

Check if current date is between two dates Oracle SQL

4 Answers4

TSQL: Dates- need to look for gaps in dates between Two Date

Linked