Most efficient way to search by date in SQL Server database

Question

In a SQL Server database, I have a table with a DateTime column and I want to get rows for a specific date.

I have found many options, like:

1. WHERE myColumn BETWEEN '2020-10-10T00:00:00.00' AND '2020-10-10T23:59:59.999'
2. WHERE CAST(myColumn AS date) = '2020-10-10'
3. WHERE date LIKE '%2020-10-10%'

...

Which one of them is the fastest?

Answer 1

As mentioned by @marc_s: None of these options are correct.

• WHERE myColumn BETWEEN '2020-10-10T00:00:00.00' AND '2020-10-10T23:59:59.999'
  This option does not correctly handle the end point. For datetime values you will get a rounding error that will include the begnning of the next day, and for datetime2 you will miss 1 millisecond.
• WHERE CAST(myColumn AS date) = '2020-10-10'
  This option, while there is some handling in the compiler for it, partially prevents indexes being used properly. The cardinality estimation will almost certainly be off.
• WHERE date LIKE '%2020-10-10%'
  This is the worst: it converts the date to a string, completely preventing usage of indexes.

What you actually need is:

WHERE myColumn >= '2020-10-10' AND myColumn < '2020-10-11'

This covers a date range and allows an index seek over that range. See also What do BETWEEN and the devil have in common?

You obviously still need an index on the date column, but as we can't see the rest of your query or your table definition we can't help with that.