title: Converting multiple date formats to a DateTime value in SQL Server
body: <p>I'm writing a SQL script to migrate data from an old table to a new one, and in the process, to put some rigour into the data by using proper data-types.</p>
  
  <p>There's a varchar field in the old table which holds dates in various formats. So far I've discovered at least 6 formats for the dates in this field:</p>
  
  <pre><code>dd/mm/yyyy
  dd/mm/yy
  dd.mm.yy
  dd-mm-yyyy
  ddxx mmmm yyyy
  dd.m.yy
  </code></pre>
  
  <p>I want to convert all of these dates into proper date-time values.</p>
  
  <p>The post <a href="https://stackoverflow.com/questions/207190/sql-server-string-to-date-conversion">here</a> about the CONVERT function is very helpful.</p>
  
  <p>However, I'm wondering how to go about <em>detecting</em> which of the above formats apply for each row being processed.</p>
  
  <p>One option is to execute a CONVERT for each possible format within a TRY/CATCH block and just use whichever one doesn't fail.</p>
  
  <p>The only other option might be string manipulation to detect the format, but that might be awfully difficult to write myself. If that was the only option I'd probably ditch SQL and write the migration as a C# application (only then it would take much longer to run than a native SQL Script).</p>
  
  <p>Does anyone know of an easier way of doing this in SQL Server?</p>