TRY_CONVERT: Checking convertability in SQL Server 2012


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.


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


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.


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.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s