Optimized date compare in WHERE clause, convert or datediff=0?

Question

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?

Shouldn't those two be CONVERT(date, ...) ? – ypercubeᵀᴹ Feb 04 '16 at 07:42 — ypercubeᵀᴹ, Feb 04 '16 at 07:42

Daniel Hutmacher · Answer 1 · 2016-02-04T07:43:20.160

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.

edited Feb 04 '16 at 07:43

answered Feb 04 '16 at 07:30

Daniel Hutmacher

9,113
1
25
52

4

Martin Smith has a great answer on a related question: Cast to date is sargable but is it a good idea? – ypercubeᵀᴹ Feb 04 '16 at 07:37
1

@ypercubeᵀᴹ thanks, I just learned something! :) – Daniel Hutmacher Feb 04 '16 at 07:42

score 2 · Answer 2 · answered Feb 04 '16 at 22:52

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

Optimized date compare in WHERE clause, convert or datediff=0?

2 Answers2