how to query DB With long time against shot time in asp.net core

Question

I have a date input field with the same day as what is in the database but different time

for instance, the date in the database table like this 2020-06-30 17:50:01.376

The input date from user is like 2020-06-30 00:00:00 I can determine the date users will input so I decided to use short date like 2020-06-30

This is my query that is returning null

product = conn.QuerySingleOrDefault<Products>("select TranDate, ProductNo from Products where ProductNo =@ProductNo AND TranDate =@TranDate", new { ProductNo = item.ProductNo , TranDate = "2020-06-30" });

It works fine if But if I change it to

product = conn.QuerySingleOrDefault<Products>("select TranDate, ProductNo from Products where ProductNo =@ProductNo AND TranDate =@TranDate", new { ProductNo = item.ProductNo , TranDate = "2020-06-30 17:50:01.376" });

Answer 1

Update your Query as below. Cast the db column date time to date and compare it with your input parameter.

SELECT TranDate
    ,ProductNo
FROM Products
WHERE ProductNo = @ProductNo
    AND CAST(TranDate AS DATE) = @TranDate