Search on date value

[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

 

[WyldGynjer]

One thought - couldn't you write your statement where you are searching between two dates.

Say the user wants all the dates for 6/24/2004 - then put in your query where date < 6/25/04 AND date > 6/23/2004. This should return anything on 6/24/2004 regardless of time...

 

[chiph]

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 BETWEEN '" & dtmDateOrdered.Date.ToString() & " 00:00:00' AND '" & dtmDateOrdered.Date.ToString() & " 23:59:59'"

Chip H.



If you want to get the best response to a question, please check out FAQ222-2244 first