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?

1
  • 2
    Shouldn't those two be CONVERT(date, ...) ? Commented Feb 4, 2016 at 7:42

2 Answers 2

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.

2
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.