title: Comparing equality of date and datetime in SQL Server
body: <p>I am returning rows based on a <code>date</code> field equaling a <code>datetime</code> field. They obviously only directly match when in the format of <code>dd/MM/yyyy = dd/MM/yyyy 00:00:00</code> but I am looking to disregard the time.</p>
  
  <p>There are 3 methods which I have tried, they all work, but I am wondering what is best.</p>
  
  <p>1 - <code>CONVERT(varchar(10),MyDate,103) = CONVERT(varchar(10),MyDateTime,103))</code></p>
  
  <p>2 - <code>MyDate = CONVERT(date,MyDateTime)</code></p>
  
  <p>3 - <code>MyDate = CAST(MyDateTime AS date)</code></p>
  
  <p>4 - <code>MyDate = DATEADD(dd, DATEDIFF(dd, 0, MyDateTime), 0)</code></p>
  
  <p>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:</p>
  
  <p>1 - 303ms average</p>
  
  <p>2 - 284ms average</p>
  
  <p>3 - 273ms average</p>
  
  <p>4 - 1745ms average</p>
  
  <p>Test is from a sample size of ~300,000</p>
  
  <p>Is there a reason for this? Is the first option genuinely the best option?</p>
  
  <p>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 <code>DATEADD/DATEDIFF</code> method Tim Schmelter mentioned below. That seems to be by far the least efficient.</p>