When to Use TRY_CONVERT() vs CONVERT() in SQL Server

Both CONVERT() and TRY_CONVERT() in SQL Server are used to convert data types, but they behave quite differently when something goes wrong. Understanding that difference can save you a lot of debugging time, especially when dealing with messy or unpredictable data.

Let’s look at when you should use each, and walk through an example that you can run yourself.

Understanding the Difference

As mentioned, both functions do the same thing (convert data types), but they behave differently when the conversion fails:

CONVERT(): You give it a value and a target data type, and it tries to convert the value. If it can’t, SQL Server throws an error, and your entire query fails. This will happen if you try to convert ABC to an integer for example.
TRY_CONVERT() works almost the same way, except it fails gracefully. Instead of throwing an error, it simply returns NULL when the conversion doesn’t work. This makes it much safer when dealing with data that might not always be clean. So if we try to convert ABC to an integer, it will return NULL instead of an error.

Having the choice between these two functions can be quite handy. You might choose CONVERT() in some scenarios and TRY_CONVERT() in others.

When to Use `CONVERT()`

Use CONVERT() when:

You know your data is clean and valid.
You want the query to fail if conversion fails (for data integrity reasons).

Here’s what happens if we try to convert ABC to an integer:

SELECT CONVERT(INT, 'ABC');

Output:

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

We get an error.

When to Use `TRY_CONVERT()`

Use TRY_CONVERT() when:

You’re unsure if all the data can be safely converted.
You want to handle conversion failures without breaking the query.
You’re performing bulk conversions or data cleaning.

For example, converting a column of mixed data (numbers and junk text) into integers.

Here’s what happens if we use TRY_CONVERT() for the previous conversion:

SELECT TRY_CONVERT(INT, 'ABC');

Output:

NULL

This time we got NULL instead of an error.

Using Both

It’s actually possible to use both together in the same query. For instance, first test your data with TRY_CONVERT(), and only then use CONVERT() on rows that passed.

Suppose we create and populate the following table:

DROP TABLE IF EXISTS dbo.ConversionDemo;
GO

CREATE TABLE dbo.ConversionDemo (
    SampleValue NVARCHAR(20)
);

INSERT INTO dbo.ConversionDemo (SampleValue)
VALUES ('123'), ('456'), ('ABC'), ('789'), (NULL);

We can run a query like the following against that table:

SELECT 
    SampleValue,
    CONVERT(INT, SampleValue) AS SafeValue
FROM dbo.ConversionDemo
WHERE TRY_CONVERT(INT, SampleValue) IS NOT NULL;

Result:

SampleValue  SafeValue
-----------  ---------
123          123      
456          456      
789          789

This approach allows us to only convert valid data and skip the rest.

Summary

CONVERT() is strict and stops on errors.
TRY_CONVERT() is forgiving and replaces errors with NULL.

Use the one that fits your tolerance for bad data. And don’t forget to test your conversions before running them on critical tables.

Understanding the Difference

When to Use CONVERT()

When to Use TRY_CONVERT()

Using Both

Summary

When to Use `CONVERT()`

When to Use `TRY_CONVERT()`