title: MSSQL Most optimal query for date range for a day, or day range
body: <p>I wanted to find out what is the "best practices" approach to a query against a record set of datetime with a date (no time). </p>
  
  <p>I use several queries that return records based on a date range, from a recordset that uses a datetime data type, which means each record needs to be checked using a between range.</p>
  
  <p>Example of a query would be: </p>
  
  <pre><code>Select * 
  FROM Usages 
  where CreationDateTime between '1/1/2012' AND '1/2/2012 11:59:59'
  </code></pre>
  
  <p>I know using <code>BETWEEN</code> is a resource hog, and that checking a datetime data type of a date is always going to be very resource intense, but I would like to hear what others use (or would use) in this situation.</p>
  
  <p>Would I get any type of performance increase converting the datetime record to a Date  like: </p>
  
  <pre><code>Select * 
  FROM Usages 
  where CONVERT(DATE,CreationDateTime) between '1/1/2012' AND '1/2/2012'
  </code></pre>
  
  <p>Or possibly doing a check of less then / greater then?</p>
  
  <pre><code>Select * 
  FROM Usages 
  where (CreationDateTime &gt; '1/1/2012') 
      AND (CreationDateTime &lt; '1/2/2012 11:59:59')
  </code></pre>