CODEWITHSUNDEEP
sqlsql-serverdatedatetimecomparison
anothershrubery
anothershrubery

Reputation: 21003

Comparing equality of date and datetime in SQL Server

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.

Upvotes: 6

Views: 14667

Answers (2)

Salman Arshad
Salman Arshad

Reputation: 272106

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

Check if MyDateTime lies between [MyDate, MyDate + 1 DAY). If there is an index on that column then this query can use index seek instead of index scan.

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

Upvotes: 1

Derik
Derik

Reputation: 296

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.

Upvotes: 4

Related Questions