How to select rows for a specific date, ignoring time in SQL Server 2005 or earlier

Question

Given a table with a datetime column, how do I query for rows where the date matches the value I specify but ignores the time portion?

For example, select * from sales where salesDate = '11/11/2010'

For this query we don't care about the time. Other queries require the time component so we can't store only the date component.

Thanks!

Answer 1

You can remove the time component when comparing:

SELECT * 
FROM sales 
WHERE CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, salesDate))) = '11/11/2010'

Another approach is to change the select to cover all the time between the start and end of the date:

SELECT * 
FROM sales 
-- WHERE salesDate BETWEEN '11/11/2010 00:00:00.00' AND '11/11/2010 23:59:59.999'
WHERE salesDate BETWEEN '2020-05-18T00:00:00.00' AND '2020-05-18T23:59:59.999'