Optimized date compare in WHERE clause, convert or datediff=0? In my query I am comparing two dates in the WHERE clause
once using CONVERT:
CONVERT(day,InsertedOn) = CONVERT(day,GETDATE())

and another using DATEDIFF:
DATEDIFF(day,InsertedOn,GETDATE()) = 0

Here are the execution plans
For 1st one using CONVERT

For 2nd one using DATEDIFF

The datatype of InsertedOn is datetime.
Which one is more optimized?
Instead, try

WHERE InsertedOn&gt;=CAST(GETDATE() AS date) AND
      InsertedOn&lt;DATEADD(day, 1, CAST(GETDATE() AS date))


This expression is sargable which is what you want for optimum performance. Like @Mikael indicates, you would do well to design one of your indexes so that InsertedOn is the first column, and that all the other columns used in the query are included in the index. If you have lots of columns in the query, using the table's clustered index is probably best. Read up on covering indexes.