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.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblDateTimeToConvert]’) AND type in (N’U’))
DROP TABLE [dbo].tblDateTimeToConvert
GO

Create table tblDateTimeToConvert (
col1 varchar(25) null)
GO

Now let’s populate our table with the following code:

Insert into tblDateTimeToConvert
values(‘Some value’),
(‘Dec 20 1988 6:31PM’),
(‘Nov 15 1982 4:57AM’),
(‘Oct 10 1985 5:06PM’),
(‘Another value’),
(‘May 26 1971 8:34AM’),
(‘Jun 1 1973 6:28AM’),
(‘Jan 17 1976 10:16PM’),
(‘Sep 24 1977 12:34PM’),
(‘Yet another value’),
(‘Aug 11 1976 5:48PM’),
(‘Jan 13 1971 4:23AM’),
(‘Jan 26 1964 1:25AM’),
(‘This is not a date’),
(‘Apr 22 1973 9:34PM’),
(‘Oct 26 1963 6:35AM’),
(‘Sep 24 1954 5:32PM’),
(‘Aug 7 1981 2:28PM’),
(‘Dec 28 1979 8:47AM’),
(‘Mar 23 1972 1:06AM’),
(‘May 7 1978 3:46PM’),
(‘Jan 24 1957 10:47AM’),
(‘Apr 2 1986 6:40PM’),
(‘Feb 17 1967 6:00AM’),
(‘Jun 7 1983 9:13PM’)

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:

First, we need a table to insert into. Let’s create it:

Create table tblDateTime (
DT datetime null,
OtherValue varchar(50) null)
GO

Now we can write our insert statement:

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