0

I have a table with a column, [MyDate], of datatype DATE.
I have a variable @MyDateTime DATETIME.

Is the following comparison:
WHERE [MyDate] < @MyDateTime
semantically equivalent to:
WHERE CONVERT(DATETIME, [MyDate]) < @MyDateTime (in SQL Server 2012)? (Meaning the two comparisons will always produce the same results.) If not, what is a set of values for which the two are not the same?

This question is motivated by the fact that I observed that the former comparison allows me to more efficiently make use of an index on [MyDate] while the latter does not.

Joe
  • 418
  • 4
  • 12
  • It's equivalent if the MyDateTime has a time of 00:00:00 as in they will return the same results. – S3S Aug 22 '18 at 19:19
  • 3
    Second is not SARGable https://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable#799616 – Lukasz Szozda Aug 22 '18 at 19:19
  • @scsimon what about when MyDateTime does not have a time of 00:00:00? From my initial tests, they are still the same. – Joe Aug 22 '18 at 19:22
  • Well that's because you have < I see... Another operand, like = would return different results. Or, `where MyDateDime > MyDate – S3S Aug 22 '18 at 19:24
  • 2
    Yes, there is an implicit conversion. See SQL Server's [documentation on data type precedence](https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-2017) for details. – Joe Farrell Aug 22 '18 at 19:25

1 Answers1

1

They are equivalent logically, but not functionally.

[MyDate] < @MyDateTime

is the same as

CONVERT(DATETIME, [MyDate]) < @MyDateTime

but I would write it as this

[MyDate] < CONVERT(DATE,@MyDateTime)

This would eliminate some of those edge cases where you are doing date vs datetime comparisons and forget about the hours and minutes; sadly, I've seen this multiple times during my career (thankfully not my mistakes).

Generally, if you wrap a column in a function as the function must be applied to all rows to determine the validity of the comparison, so the index can't be leveraged optimally. But as Gordon Linoff stated, the index is still be used based on my testing and is no slower when casting to a DATETIME on an indexed DATE column.

UnhandledExcepSean
  • 12,504
  • 2
  • 35
  • 51
  • 1
    It turns out that indexes *can* be used when converting a `datetime` to a `date`. You can call it the exception that provides the rule. – Gordon Linoff Aug 22 '18 at 19:26
  • @GordonLinoff That is trickery if it works! I'll have to test. – UnhandledExcepSean Aug 22 '18 at 19:27
  • 1
    . . You might be interested in this blog: https://www.experts-exchange.com/articles/2531/SARGable-functions-in-SQL-Server.html. – Gordon Linoff Aug 23 '18 at 01:33
  • @GordonLinoff That IS an interesting read; especially the suggestion he forwarded to Microsoft. – UnhandledExcepSean Aug 23 '18 at 13:17
  • In my query I ended up using `[MyDate] < @MyDateTime` since it used the index on `[MyDate]`. `CONVERT(DATETIME, [MyDate]) < @MyDateTime` also used the index as @GordonLinoff mentioned, but in my case the estimated execution plan also used a (non-seek) predicate and therefore seemed to be slightly less efficient than the first comparison. – Joe Aug 23 '18 at 17:02