CODEWITHSUNDEEP
sqloracle-database
user2093576
user2093576

Reputation: 3092

oracle in between date not working

select * from table where EMPNO='123'  
and TO_DATE('08-Dec-2015','DD-MON-YYYY') between  EFF_FROM_DATE  and EFF_TO_DATE ;

I have two rows with data :

EFF_FROM_DATE            EFF_TO_DATE 
25-OCT-2015 15:45:19    08-DEC-2015 09:22:17
08-DEC-2015 09:22:17    31-DEC-9999 00:00:00

but this query is fetching data only for -

25-OCT-2015 15:45:19 08-DEC-2015 09:22:17

what am I doing wrong in this query?

Upvotes: 0

Views: 4098

Answers (2)

viduka
viduka

Reputation: 153

i think you need trunc column EFF_FROM_DATE and EFF_TO_DATE. something like this:

select * from table where EMPNO='123'  
and TO_DATE('08-Dec-2015','DD-MON-YYYY') between  TRUNC(EFF_FROM_DATE)  and (EFF_TO_DATE);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You probably intend this:

select *
from table
where EMPNO = '123' and 
      DATE '2015-12-08' >= trunc(EFF_FROM_DATE) and
      DATE '2015-12-08' < trunc(EFF_TO_DATE) + interval '1' day;

The problem is that the time component affects the comparison. The "from" date has a time component, so date '2015-12-08' is less than '2015-12-08 10:09:22'. Hence the original logic doesn't work.

Upvotes: 1

Related Questions