CODEWITHSUNDEEP
sqloracle-database
user2093576
user2093576

Reputation: 3092

Getting data between dates is not accurate

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'

Upvotes: 3

Views: 91

Answers (3)

Praveen
Praveen

Reputation: 9335

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)

Upvotes: 4

David Aldridge
David Aldridge

Reputation: 52336

Never do this:

CREATED_DATE >= '11-MAY-2015'

The result you get will be dependent on your NLS settings for the server, client, and/or session, as you are comparing a date with a string literal, requiring the string literal to be implicitly converted to a date.

Always convert literals strings to literal dates to do a comparison of this sort. The most simple way is:

CREATED_DATE >= date '2015-05-11'

... but if you have to accept date strings in another known format then:

CREATED_DATE >= To_Date('11-MAY-2015', 'DD-MON-YYYY')

In this case, you probably need to:

CREATED_DATE >= date '2015-05-11' and
CREATED_DATE <  date '2015-05-12'

Upvotes: 1

fez
fez

Reputation: 1835

Depending on the CREATED_DATE datatype You may need to use the TO_DATE function. Try this:

select * from TABLE where 
CREATED_DATE >= TO_DATE('2015-05-11 00:00:00', 'YYYY-MM-DD HH24:Mi:SS') 
and CREATED_DATE <= TO_DATE('2015-05-12 23:59:59', 'YYYY-MM-DD HH24:Mi:SS')

Upvotes: 3

Related Questions