5

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 enter image description here

For 2nd one using DATEDIFF enter image description here

The datatype of InsertedOn is datetime.

Which one is more optimized?

Community
  • 1
Kazi Lakit
  • 93
  • 1
  • 1
  • 4

2 Answers2

7

Instead, try

WHERE InsertedOn>=CAST(GETDATE() AS date) AND
      InsertedOn<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.

Daniel Hutmacher
  • 9,173
  • 1
  • 27
  • 52
2

Would it not be better to have the dates as variables so you only have to get them once before the query?

DECLARE @Today DATE
DECLARE @Tomorrow DATE
SET @Today = GETDATE()
SET @Tomorrow = DATEADD(day,1,GETDATE())

SELECT FROM <Table>
WHERE InsertedOn >= @Today
AND InsertedOn < @Tomorrow
Lee Walters
  • 96
  • 4