CODEWITHSUNDEEP
oracle-databasedate-arithmetic
Kevin
Kevin

Reputation: 3509

query by day in PL/SQL(oracle)

I am querying from database: 

select * from database where id = 12345

and i get a couple of days where it is equal to 3/4/2010 9:16:59 AM

but if i add and date = to_date('03/04/2010','DD/MM/YYYY')

giving me

select * from database where id = 12345
and date = to_date('03/04/2010','DD/MM/YYYY')

I comeback with completely nothing.

Any pointers? btw, I know that there is a time on there, but I don't know how to compare just based on the day!!!

Upvotes: 0

Views: 3446

Answers (2)

beny23
beny23

Reputation: 35018

That's because when you are creating the date, you implicitly set the time to 0:00:00, and as 0:00:00 is not equal to 9:16:59, you'll not get the date returned.

To tell Oracle to ignore the time part, just do the following:

WHERE id = 12345
  AND trunc(date) = to_date('03/04/2010', 'DD/MM/YYYY')

Upvotes: 3

Markus Winand
Markus Winand

Reputation: 8716

 WHERE id = 12345
   AND date >= TO_DATE('03/04/2010', 'DD/MM/YYYY')
   AND date <  TO_DATE('03/04/2010', 'DD/MM/YYYY') + INTERVAL '1' DAY

http://use-the-index-luke.com/sql/where-clause/obfuscation/dates

Upvotes: 1

Related Questions