Date from Date/Time Field

I have what I'm assuming is an easy question to answer, but I haven't found the answer yet.

I am storing date and time info in a SmallDateTime field. The problem is when I try to retrieve records for a specific date.

For example,

"SELECT field_date FROM table WHERE field_date = '4/15/2002';"

This SQL statement returns no data. I'm assuming it's looking for an exact match, which it won't find due to the time info.

I tried to use LIKE, but SQL Server didn't like LIKE.

How do I retrieve records by date only?

Thanks.three options:

store the dates without time, essentialy making the time part midnight.

convert "field_date" to a string, this can be slow as you will scan the entire table.

change the where caluse to search using between "where filed_date between '4/15/2002' and '4/15/2002 23:59:59:999'"