Datetime conversions

Ever tried to do a date conversion on two date fields, where you wanted to compare the date only ? Ever waited forever for the query to complete ?

Fear not ! The answer i right here:

Usually one would do the conversion by converting the two datetime fields to strings, and strip the time part:

left(convert(varchar(30),SomeDateTime,120),10) =
left(convert(varchar(30),OtherDateTime,120),10)

However, this will induce an internal conversion in SQL Server, effectively converting all records for both datetime fields to strings. Furthermore the LEFT string operator will take quite some effort til calculate, of you are handling +10 million records.
The alternative suggested by the article (link below) gives quite another expression:

DATEADD(d,DATEDIFF(d,0,SomeDateTime),0) =
DATEADD(d,DATEDIFF(d,0,OtherDateTime),0)

This statement does not induce a full table scan, and will therefore make use of an index (if one exists) on the datetime fields.

I was able to speed up a query that took 40 seconds, down to less than 1 second by this method.

Follow the white rabbit to the source of all SQL Server knowledge..
(www.sqlserverperformance.com)