SQL 2012: Path to Easier Data Cleanup with Try_Convert

When using the convert function in previous versions of SQL Server, inserts would fail if SQL Server hit a value that didn’t conform to the destination table’s data type. This can be very frustrating for DBAs or Database Developers because SQL Server also didn’t tell you which value it failed to convert. If the source table didn’t have many records this wasn’t a big deal but in most cases, there are many records to convert. Sometimes, you have several insert statements, and tracking everything down costs all sorts of time.

A new feature in SQL 2012 that I am very excited about is TRY_CONVERT, which makes data conversion much easier. When you pair TRY_CONVERT with some Case statements, you can easily pipe the inconvertible data to other columns, which can then be reviewed and (hopefully) fixed prior to cutting over your data.

I’ve mocked up a quick demo so you can see how powerful this new feature is.

First up, let’s create a table with a Varchar column to hold some potential dates.

You’ll notice that these are mostly valid dates, with a few records that are obviously not dates. Now that we have some data, let’s try to convert it and see what happens with the “Convert” function:

Query:
Select convert(datetime, col1) from tblDateTimeToConvert.

Error:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

Let’s see what happens with Try_Convert:

Select try_convert(datetime, col1) from tblDateTimeToConvert.

As you can see, it succeeds, placing Null for any values that cannot be converted. That’s pretty cool. This code will always succeed when faced with conversion errors – but what if you’d like to have someone reviewing the previous values? It may just be that the dates are incorrectly formatted. That’s when you can do something like this:

INSERT INTO [dbo].[tblDateTime] ([DT], [OtherValue])
Select Case when TRY_CONVERT(datetime, col1) is null then null
else TRY_CONVERT(datetime, col1) END [DT],
Case when TRY_CONVERT(datetime, col1) is null then col1 END [OtherValue]
From tblDateTimeToConvert

Let’s look at the data:

Select *
from tblDateTime

As you can see in the results, we’ve inserted null records into our conversion column but retained the original values in another column. We can easily write reports for someone with business knowledge to review. A query for a report like that could look something like this:

Select DT, OtherValue
from tblDateTime
Where DT is null
and OtherValue is not null