title: Why this Query is returning different results for different date formats?
body: <p>I have a table named book_data with batch_dt as column name of type varchar in sql server.</p>
  
  <p>when I pass the query </p>
  
  <pre><code>SELECT DISTINCT batch FROM book_data 
  </code></pre>
  
  <p>it gives me the following results</p>
  
  <pre><code>  batch_dt
  -------------
   2012-10-31
  -------------
   2012-11-01
  -------------
   2012-11-02
  -------------
   2012-11-03
  -------------
        .
        .
        .
  </code></pre>
  
  <p>Now what I am doing is getting the total count of records between two dates. Fairly a simple query.</p>
  
  <pre><code>SELECT COUNT(*) FROM book_data WHERE CONVERT(varchar(12),CONVERT(datetime,batch_dt),101) BETWEEN '11/02/2012' and '10/31/2012'
  </code></pre>
  
  <p>the result is 
  <strong>112</strong></p>
  
  <p>and just by changing the month from <strong>02</strong> to <strong>2</strong> the query gives me <strong>218</strong> results </p>
  
  <pre><code>SELECT COUNT(*) FROM book_data WHERE CONVERT(varchar(12),CONVERT(datetime,batch_dt),101) BETWEEN '11/2/2012' and '10/31/2012'
  </code></pre>
  
  <p>why this  different behaviour?</p>