CODEWITHSUNDEEP
sqlsql-serverdatedatetimesql-server-2012
Joe
Joe

Reputation: 468

Is comparing a date to datetime the same as first casting the former to a datetime in SQL Server?

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.

Upvotes: 0

Views: 65

Answers (1)

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

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.

Upvotes: 1

Related Questions