query by day in PL/SQL(oracle)

I am querying from database:

select * from database where id = 12345

a开发者_StackOverflownd 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!!!

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')

 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