You might have noticed that T-SQL includes both a CONVERT() function and a TRY_CONVERT() function that you can use in SQL Server to perform conversions between data types. But if you’re scratching your head over what the difference is between these two functions, read on!

The difference between CONVERT() and TRY_CONVERT() is in the way they handle data types that can’t be converted. One throws an error, while the other returns null. The following examples demonstrate this.

The CONVERT() Function

First, let’s look at what the CONVERT() function does. It converts one data type to another. Here’s an example:

SELECT 'Comments: ' + CONVERT(varchar(12), 9) AS Result;

Result:

Result     
-----------
Comments: 9

In this example, we convert an int value to varchar(12). And because the value was able to be converted into our desired data type and length, this worked perfectly.

However, while this works perfectly when the conversion succeeds, what happens when it fails?

When Conversion Fails → Error

Let’s tweak the code a bit and see what happens when the conversion fails:

SELECT 'Comments: ' + CONVERT(varchar(1), 10.00) AS Result;

Result:

Error: Arithmetic overflow error converting numeric to data type varchar.

We get an error.

The TRY_CONVERT() Function

The TRY_CONVERT() is an alternative that we can use to prevent the error message. This function performs the same data conversion operation as CONVERT(), however, if this function can’t perform the conversion, it returns null:

SELECT 'Comments: ' + TRY_CONVERT(varchar(1), 10.00) AS Result;

Result:

Result
------
null

You can take this result and apply conditional code so that a different thing happens depending on whether or not the conversion succeeds.

Example:

SELECT   
    CASE WHEN TRY_CONVERT(varchar(1), 10.00) IS NULL   
    THEN 'Conversion failed'  
    ELSE 'Conversion succeeded'  
END AS Result;

Result:

Result           
-----------------
Conversion failed

Invalid Data Types

One caveat with TRY_CONVERT() is that it only works this way when using valid data types. So you’ll get an error if you explicitly supply a data type that’s not permitted.

Example:

SELECT 'Comments: ' + TRY_CONVERT(Homer, 10.00) AS Result;

Result:

Error: Type Homer is not a defined system type.