Using TRY_CONVERT in SQL Server 2012+ to migrate from varchar fields to strongly-typed fields

We have a SQL 2008 database which has some varchar fields used to store times. I wanted to migrate them to use actual time fields, but the format entered into the varchar field had been inconsistent (as previously, there was no validation on the field). In addition to properly formatted times, some entries used . instead : (for example, 12.38), some entries had am/pm (or just a/p) on the end, some didn’t. A simple convert(time, …) would not suffice.

One way to approach is to TRY_CONVERT (new in SQL 2012: converts if able, returns null otherwise) and chain them with COALESCE in order to try multiple formats for a single field.

Consider the following example, assuming field is the source varchar field and convertedField is the new time-typed field.

UPDATE tableName SET
  convertedField = COALESCE(
   TRY_CONVERT(time, field),
   TRY_CONVERT(time, REPLACE(field, '.', ':')),
   TRY_CONVERT(time, REPLACE(field, 'a', ''),
   ...
)	  

Two other things to consider with a conversion of this type:

1. Users may mix 12 and 24 hour time standards. In this case, we had entries like 13:30 (clear) and 1:30 that were used to reflect the same time. Some additional logic and testing is needed to attempt to “smart migrate” afternoon times that are not marked with pm and are in 12-hour format.
2. If the database is being imported from SQL 2008, you need to upgrade the compatibility level to gain access to 2012 features:

   ALTER DATABASE DatabaseName
   SET COMPATIBILITY_LEVEL = 110;