title: How to standardise a column of mixed date formats in T-SQL
body: <p>Got a table in SQL Server which contains a <code>varchar</code> column with date data. Unfortunately the dates are in a whole slew of different formats.</p>
  
  <pre><code>2012-05-01
  27/05/2012 
  07MAY2014
  19/07/13   
  </code></pre>
  
  <p>There may be others, but that's all I've encountered so far.</p>
  
  <p>I need to squeeze these into a <code>datetime</code> column into another table, so I've been trying to select them as standard date-time values. At first, I thought that'd be easy:</p>
  
  <pre><code>UPDATE myTable 
  SET myDateColumn = CONVERT(DATETIME, myDateColumn, 103)
  WHERE ISDATE(myDateColumn) = 0
  </code></pre>
  
  <p>But the trouble is that SQL Server treats <code>dd/mm/yy</code> and <code>dd/mm/yyyy</code> as separate formats. The former is code 3, and the latter is code 103. So whichever way I run that update, it chokes on the opposite format.</p>
  
  <p>Is there any way I can select/update based on the date format, and get all these dates converted to a single valid <code>DateTime</code> format?</p>