6

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.

anothershrubery
  • 20,461
  • 14
  • 53
  • 98
  • 2
    I'm using the `DATEADD/DATEDIFF` approach. http://stackoverflow.com/questions/1177449/best-approach-to-remove-time-part-of-datetime-in-sql-server – Tim Schmelter Oct 31 '13 at 15:08
  • In reference to the close request, I don't think this is a duplicate because this kind of goes against the solution in the other question. Here the string conversion is the quickest! Edited question to take into account `DATEADD/DATEDIFF` – anothershrubery Oct 31 '13 at 15:42
  • 1
    What is the sample size on which this average is based? – Dan Bracuk Oct 31 '13 at 15:53
  • Approximately 300,000 – anothershrubery Oct 31 '13 at 16:27
  • If you are selecting from a table, then your test is flawed because you are not testing just the conversion and comparison, but also the execution plan, index usage, or lack thereof. It would help to see your entire test. – Matt Johnson-Pint Nov 01 '13 at 16:41

2 Answers2

4

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.

SQL Hammer
  • 276
  • 5
  • 16
  • 1
    Further to this, wrapping a field in a function makes it non sargeable, meaning an index can't be used if it has one. This is another good reason for #3 – Nick.Mc May 14 '15 at 05:20
  • @Nick.McDermaid isn't `CAST` a function? – Salman A Nov 12 '15 at 18:33
  • @Salman it's not clear from the post but the assumption is that `MyDate` is a column and `MyDateTime` is not a column. – Nick.Mc Nov 12 '15 at 22:46
1

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
Salman A
  • 262,204
  • 82
  • 430
  • 521