Index seeks on date and CONVERT

~ franklinyamamoto

Using functions on columns typically prevents SQL Server from being able to efficiently use an index with a seek (sargability).  When it comes to DATE and DATETIME data types, there are some exceptions.

A common use case I see is where a column contains a DATETIME data type; however, a query needs to look at a date without the time part.  The following is a typical query that would do this:

SELECT * from CustomerAccount WHERE CONVERT(DATE, ModifiedDate) = '2022-01-30'

I would typically expect something like this to scan the ModifiedDate column due to a function (CONVERT) being applied to that column.  It turns out SQL Server makes some exceptions to this where it can still seek on the indexed ModifiedDate:

You can see the seek is performing a range > and < on the ModifiedDate column:

So if you have complex logic like this to avoid conversions on a DATETIME column it’s not necessary:

SELECT * from CustomerAccount WHERE ModifiedDate >= CONVERT(DATE, @ModifiedDate) AND ModifiedDate < DATEADD(dd, 1, CONVERT(DATE, @ModifiedDate))

Happy tuning!