Two New Convert Functions in SQL Server 2012

Continuing my series on SQL Server 2012, today we will explore two new Convert functions in SQL Server 2012. The new two convert functions available in SQL Server 2012 are try_convert() and try_parse(). In earlier versions, when you convert an expression to a specific datatype, an error would occur if the expression is invalid for that datatype. But these new functions do not throw an error, instead they convert the result to NULL.

Consider this example

select cast('a' as int)

This will procedure the error

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'a' to data type int.

The error occurs as string value 'a' cannot be converted to an integer datatype, as it is not a number.

When you use try_convert() function, it will produce NULL instead of an error message

select try_convert(int ,'a')

This function handles the error efficiently for you.

try_parse() is also similar to try_convert() but you can only convert data into numbers or date or time values.

select try_parse('2000/3/4' as datetime)  

The above coverts the string to a valid date of datetime datatype.

If you have some data and if you want to know which are valid dates, you can effectively use the try_convert() function in the following manner

declare @dates table(dates varchar(100))
insert into @dates (dates)
select '20000101' union all
select '20009' union all
select '19-feb-2012' union all
select 'test' union all
select '11.11.2007'

select dates from @dates
where try_convert(datetime,dates) is not null

If the string is not a valid date, this function will make it NULL so using NOT NULL in the where clause will exclude invalid dates.