CODEWITHSUNDEEP
sqloracle-database
xjliao
xjliao

Reputation: 23

Oracle date between and?

Why is the results not include '2013-10-14'

select * 
  from table t 
 where t.f_date between to_date('2013-10-20', 'yyyy-mm-dd') and
                        to_date('2013-10-14', 'yyyy-mm-dd')

Upvotes: 0

Views: 151

Answers (2)

steve godfrey
steve godfrey

Reputation: 1234

This could be caused by the time component issue mentioned in other answers, however you will also need to change your between to have the earliest date first, otherwise it will never return anything.

e.g.

SELECT *
FROM dual
WHERE SYSDATE BETWEEN SYSDATE AND SYSDATE + 1;

will return a 1 record, but 

SELECT *
FROM dual
WHERE SYSDATE BETWEEN SYSDATE + 1 AND SYSDATE;

will not.

Upvotes: 1

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131374

Does d.f_date include a time component? If it does, the only records that will be returned by between will be those whose time is exactly 00:00:00. All others will be greater that 2013-10-14.

When comparing dates, a date without a time component is assumed to have a 00:00:00 time component

Upvotes: 6

Related Questions