CODEWITHSUNDEEP
sql-servert-sqlquery-performance
xhr489
xhr489

Reputation: 2309

Best way to filter datetime2 with a date

I have a column that is datetime2 and I want to filter using a date variable. Currently I use:

where mystringCol in ('x', 'y', 'z') and cast(MyDatetimeCol as date) > @MyDateVariable.

In the execution plan it is using a seek on the covering nonclustered index on (mystringCol, MyDatetimeCol) which has seleral columns in the include part. Regarding the predicate being SARGABLE. So it seems it is SARGABLE even though it is doing everything wrong, i.e. using a function (cast) and using in?

Also do I need the cast?

Upvotes: 1

Views: 943

Answers (1)

Thom A
Thom A

Reputation: 95564

If seems like you're overly complicating things here. The only reason I can see why you would cast MyDatetimeCol a date in the expression MyDatetimeCol > @MyDateVariable is so that a value like '2020T-11-03T11:00:00 returns false when @MyDateVariable has the value 20201103.

If that is the case, instead of using > use >= and increase the value of @MyDateVariable by one, then it is symantically the same:

DECLARE @MyDateVariable date = '20201104'; --was 20201103 before

SELECT ...
FROM ...
WHERE ...
  AND MyDatetimeCol >= @MyDateVariable;

CAST(MyDatetimeCol AS date) > @MyDateVariable and MyDatetimeCol >= DATEADD(DAY,1,@MyDateVariable) are effectively synonyms, apart from that the latter will be more performant.

Upvotes: 1

Related Questions