Using DATE part of datetime column in WHERE clause
Community wiki answer:

Try an inclusive start date and exclusive end date:

date_time &gt;= @start AND date_time &lt; DATEADD(day, 1, @end)


See also What do BETWEEN and the devil have in common? by Aaron Bertrand.



Sargability wouldn't be a problem if the query is written to use CAST(date_time AS DATE), since SQL Server will include a helper covering seek predicate. See Understanding SARGability (to make your queries run faster) by Rob Farley (recorded at SQLBits VII in 2010) which demoed this. Note however that the conversion might still affect the accuracy of cardinality estimates.