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.

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan