title: SQL Server between two datetime fields, not working correctly
body: <p>SQL Server 2005:</p>
  
  <p>The following view</p>
  
  <pre><code>  SELECT CONVERT(VARCHAR(20), keyedtimestamp, 101) as KeyedDate
      FROM TMSSTATFILE_STATS a                                                                
     WHERE (CONVERT(VARCHAR(20), a.KeyedTimestamp, 101) BETWEEN '03/01/2011' And '03/31/2011')  
  ORDER BY KeyedDate
  </code></pre>
  
  <p>Results are given for keyed dates 3/2/2011 to 3/31/2011.</p>
  
  <p>If I change the first date to 03/00/2011</p>
  
  <pre><code>  SELECT CONVERT(VARCHAR(20), keyedtimestamp, 101) as KeyedDate
      FROM TMSSTATFILE_STATS a                                                                
     WHERE (CONVERT(VARCHAR(20), a.KeyedTimestamp, 101) BETWEEN '03/00/2011' And '03/31/2011')  
  ORDER BY KeyedDate
  </code></pre>
  
  <p>it now gives data for dates 3/1/2011 to 3/31/2011</p>
  
  <p>The KeyedTimestamp field is DateTime and there are times associated with these records.  All records for 3/31/2011 are accounted for.  I know I can do this instead by supplying the max time in the second date in between, so I'm not looking for an alternative where clause, but rather an understanding of why it's ignoring the records from the first even though its incorporating the ones from the 31st.</p>
  
  <p>Its almost as if its checking for 3/1/2011 23:59:59, I was hoping I could eliminate this kind of check where I only care about the date, not the time</p>