Comparing date and time and time equality in SQL Server

Question

I am returning rows based on a date field equal to a datetime . They obviously correspond directly only in the format dd/MM/yyyy = dd/MM/yyyy 00:00:00 , but I look to ignore the time.

There are three methods that I have tried, they all work, but I wonder what is better.

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)

For me, # 1 should be the slowest conversion to string, then using string comparisons must be the least efficient. But in tests it is the fastest! Below are my tests:

1 - 303 ms average

2 - 284 ms average

3 - 273 ms average

4 - 1745 ms average

Testing is performed with a sample size of ~ 300,000

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

EDIT: modified test values ​​to display tests performed 10 times for records of 300 thousand records. Changes the result to show everything very similar except Tim Schmelter's DATEADD/DATEDIFF method, mentioned below. This is apparently the least effective.

Answer 1

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

You have already completed the performance work, so I will not redo it. Your updated numbers show that options 1-3 are very similar, so we can defer performance, except that we exclude # 4.

Once performance is resolved, it will be redesigned and understood. # 1 is definitely for most codes, and the most difficult to read, so I would rule it out. The same reason applies to the already excluded, # 4.

This leaves us with C # 2 and # 3. My choice goes to # 3, because CAST is part of the SQL standard and is more portable than CONVERT. Therefore, I would recommend always using CAST when you do not need the special CONVERT functions.

Answer 2

If MyDate is a parameter, then there is a fifth option:

Check if MyDateTime between [MyDate, MyDate + 1 DAY) . If there is an index in this column, then this query can use the index search instead of scanning the index.

 DECLARE @MyDate1 AS DATETIME = '2015-01-01' -- 2015-01-01 00:00:00 DECLARE @MyDate2 AS DATETIME = DATEADD(DAY, 1, @MyDate1) -- 2015-01-02 00:00:00 SELECT ... WHERE MyDateTime >= @MyDate1 AND MyDateTime < @MyDate2