Mitch Wheat - Treat the cause, not the symptoms!

This is not new, but I wanted to record it for my own future reference.

Developers are sometimes caught when creating WHERE clauses that compare SQL Server datetime column values that have a time portion, such as '2007-05-20 21:32:33.437' as opposed to '2007-05-20 00:00:00'. For instance, if you were to select all orders that had an OrderDate >= '2007-05-10 21:32:33.437', then all rows having the value of that date but a smaller time portion would fail the test.

There are quite a few ways to extract just the date portion of a datetime value, but several suffer from the drawback that they apply a formula to the column being tested and greatly reduce the possibility that an index can be used (i.e. non searchable arguments). The following converts @SomeDateParam to have a zero time portion in a test of all order dates greater than or equal to a given datetime:

...
WHERE DateColumn >= DATEADD(day, DATEDIFF(day, 0, @SomeDateParam), 0)

Of course, not every comparison criteria can be SARG'ed that way, but many can. How you apply such formulas depends on the type of comparison.