Search by date field - how to format

[Corinne]

I'm trying to allow the users to search a database table (SQL) by selecting a date from a datetime picker control. The date value is datetime in the table. The date is stored as 6/24/2003 11:47:00 AM as an example. I want the query to return all rows where the date is 6/24/2003. For this search I don't care about the time. I do need the time for other reasons. My question is: how do I write the query to only search for the date without the time?

This is my query:
SELECT Customers.LastName, Customers.FirstName, Orders.OrderNum, Orders.OrderMethod,
OrderItemXrefs.ItemQuantity, Items.ItemName, Orders.DateOrderRcvd
FROM OrderItemXrefs INNER JOIN Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomersID ON OrderItemXrefs.OrderNum = Orders.OrderNum INNER JOIN Items ON OrderItemXrefs.ItemCode = Items.ItemCode WHERE Orders.DateOrderRcvd = '" & dtmDateOrdered & "'"

This query works when Orders.DateOrderRcvd contains just the date. How do I write it to return correctly when the field contains the time also?

Thanks,
Corinne

[Mike Hildner]

There's probably several ways to do this, as usual, but maybe you could use BETWEEN. For example:

select * from radiomessages where [time] between '2004-03-16 00:00:00' and '2004-03-16 23:59:59'

This is MSSQL 2000 and [time] is a columns of datetime type.

Mike