ignoring timestamp in WHERE clause when comparing to date

My table h开发者_如何学JAVAas records like these

23-MAY-11 11.40.39.000000 AM

The following query brings nothing

SELECT * 
  FROM my_table 
 WHERE tenant_pha = 'test'
   AND create_date >= TO_DATE('05/10/2011','mm/dd/yyyy')
   AND create_date <= TO_DATE('05/23/2011','mm/dd/yyyy')

However, the below query will bring data

SELECT * 
  FROM my_table 
 WHERE tenant_pha = 'test'
   AND create_date >= TO_DATE('05/10/2011','mm/dd/yyyy')
   AND create_date <= TO_DATE('05/24/2011','mm/dd/yyyy')

I think this is because create_date column is time stamp.

How can I change my query to bring the desired result ( I want to avoid doing functions on the left side columns because they will make the query long).

---

You are right about the timestamp. '05/23/2011' is the same as '05/23/2011 12:00 AM'.

To include the whole day I usually move my date up by a day. < '05/24/2011' will include all of 5/23.

or change to '05/23/2011 23:59:59'

---

You can use trunc() without problems, you only need to create a function based index.

If you create this index:

CREATE INDEX idx_trunc_date ON my_table (trunc(create_date));

then the following condition will make use of that index:

AND trunc(create_date) >= TO_DATE('05/10/2011','mm/dd/yyyy')