Comparing equality of date and datetime in SQL Server

Question

I am returning rows based on a date field equaling a datetime field. They obviously only directly match when in the format of dd/MM/yyyy = dd/MM/yyyy 00:00:00 but I am looking to disregard the time.

There are 3 methods which I have tried, they all work, but I am wondering what is best.

1 - CONVERT(varchar(10),MyDate,103) = CONVERT(varchar(10),MyDateTime,103))

2 - MyDate = CONVERT(date,MyDateTime)

3 - MyDate = CAST(MyDateTime AS date)

4 - MyDate = DATEADD(dd, DATEDIFF(dd, 0, MyDateTime), 0)

To me, #1 should be the slowest, converting to string then using string comparison surely should be least efficient. But in tests it is the fastest! Below is my tests:

1 - 303ms average

2 - 284ms average

3 - 273ms average

4 - 1745ms average

Test is from a sample size of ~300,000

Is there a reason for this? Is the first option genuinely the best option?

EDIT: Changed the test values to reflect the tests being ran 10 times each for 300k records. Changes the outcome to show all are pretty similar apart from the DATEADD/DATEDIFF method Tim Schmelter mentioned below. That seems to be by far the least efficient.

Answer 1

I would say that #3 is the best choice. Here are my reasons.

You have already performed the performance work, so I won't redo it. Your updated numbers show options 1-3 to be very similar so we can put performance aside, except to rule out #4.

Once performance is settled, it's on to best practices and readability. #1 is definitely to most code and the hardest to read so I would rule that out. This same reason applies to the, already ruled out, #4.

This leaves us with #2 and #3. My selection goes to #3 because CAST is part of the SQL standard and is more portable than CONVERT. So, I would recommend always using CAST, whenever you do not need the special features of CONVERT.