First of all, don't use literals, always use
SqlParameter[
^]. This will help you with conversion problems and protect against SQL injections.
If the problem is the time portion, you can either define the column in the database as
date
if you don't need the time potion at all or if you need it, then one way is to eliminate it in your query. For the latter the querty could look something like:
SELECT Products.ProductName, Products.origionalPrice, OrderDetails.Quantity,OrderDetails.UnitCost
FROM Products LEFT JOIN OrderDetails
ON Products.ProductID=OrderDetails.ProductID
WHERE CAST(OrderDetails.PurchaseDate AS date) BETWEEN '1/4/2012' and '1/4/2012'
Or actually when parameters are correctly used, it would be:
SELECT Products.ProductName, Products.origionalPrice, OrderDetails.Quantity,OrderDetails.UnitCost
FROM Products LEFT JOIN OrderDetails
ON Products.ProductID=OrderDetails.ProductID
WHERE CAST(OrderDetails.PurchaseDate AS date) BETWEEN @startDate and @endDate