Checking convertability in SQL Server

Introduction

We may take a varchar input and to convert it to another data type before inserting/updating to tables. We mostly do an optimistic conversion expecting the varchar value will be in proper convertible format. But this approach can cause exceptions when the input is not in the expected format. 

In SQL Server 2012 a new function has been introduced similar to TryParse in .NET through we can check the convertibility.

TRY_CONVERT 

Let's say we need to check a value can be converted to float:

SELECT TRY_CONVERT(float,'4.5') 

Since  4.5 is convertible to float it will return a float with value of 4.5. 

In below example the value is NA and is not convertible.

SELECT TRY_CONVERT(float,'NA')

The output will be NULL in this case. 

By using TRY_CONVERT instead of direct CAST or CONVERT we can avoid conversion errors in the scripts.

Read http://technet.microsoft.com/en-us/library/hh230993 for details.