title: How to compare a DATETIME to a VARCHAR date that can have different lengths?
body: <p>In SQL Server 2008 R2, I have to compare two dates of birth from different tables.</p>
  
  <ul>
  <li>column <code>C.date_of_birth</code> is of type <code>DATETIME</code> </li>
  <li>column <code>R.dob</code> is a date stored as <code>VARCHAR</code>, but with no leading zeroes on the month or day parts to make its length consistent (&lt;--that's the tough part).</li>
  </ul>
  
  <p>I've gotten as close as this:</p>
  
  <pre><code>where R.dob &lt;&gt; convert(varchar, cast(C.date_of_birth as DATE), 101)
  </code></pre>
  
  <p>but that returns too many rows because <code>1/5/1923</code> in <code>R.dob</code> does not match <code>01/05/1923</code> in <code>C.date_of_birth</code>.</p>
  
  <p>How can I format <code>R.dob</code> so it can correctly be compared to <code>C.date_of_birth</code>?</p>
  
  <p>Thank you.</p>