Getting data between dates is not accurate

Question

I am trying to fetch data between two dates. My query is:

select * 
  from TABLE
 where CREATED_DATE >= '11-MAY-2015'
   and CREATED_DATE <= '11-MAY-2015'

It doesn't return any value. Though the data is present for 11th May and can be fetched if I give the dates as

CREATED_DATE  >= '11-MAY-2015'  and CREATED_DATE <= '12-MAY-2015'

Answer 1

DateType Date do have time part also.
When you say '11-MAY-2015' its actually '11-MAY-2015 00:00:00'

Date
This datatype contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It does not have fractional seconds or a time zone.

Try this query

select * 
from TABLE
where
CREATED_DATE  >= date '2015-05-11'  and CREATED_DATE < date '2015-05-12'

This will be in effect

11-MAY-2015 00:00:00 <= CREATED_DATE <  12-MAY-2015 00:00:00

Or by using Trunc which will ignore the time of the date:

select * 
from TABLE
where
Trunc(CREATED_DATE) = date '2015-05-11'

Edit: Updated the date field using date literals, as 11-MAY-2015 will only work with certain NLS settings.. (comment by @Ben and @David Aldridge)