SQL Server TRY_CONVERT Function

Summary: in this tutorial, you will learn how to use the SQL Server TRY_CONVERT() function to convert a value of one type to another.

SQL Server TRY_CONVERT() function overview

The TRY_CONVERT() function converts a value of one type to another. It returns NULL if the conversion fails.

The following illustrates the syntax of the TRY_CONVERT() function:

TRY_CONVERT (
    data_type[(length)], 
    expression 
    [,style]
)
Code language: SQL (Structured Query Language) (sql)

The TRY_CONVERT() accepts three arguments:

• data_type is a valid data type into which the function will cast the expression.
• expression is the value to cast.
• style is a provided integer that specifies how the function will translate the expression.

The TRY_CONVERT() function tries to convert the value passed to it to a specified data type. It returns the value as the specified data if the cast succeeds; Otherwise, it returns. However, if you request a conversion that is explicitly not permitted, the TRY_CONVERT() function will fail with an error.

TRY_CONVERT() vs. CONVERT()

If the cast fails, the TRY_CONVERT() function returns NULL while the CONVERT() function raises an error. This is the main difference between the two functions.

You can use the NULL handling functions or expressions such as ISNULL() and COALESCE to handle the result of the TRY_CONVERT() function in case the cast fails.

To handle the result of the CONVERT() function if the cast fails, you use the TRY...CATCH statement.

SQL Server TRY_CONVERT() function examples

Let’s take some examples of using the TRY_CONVERT() function.

A) TRY_CONVERT() returns NULL example

This example shows how the TRY_CONVERT() function returns NULL when the cast fails:

SELECT 
    CASE
        WHEN TRY_CONVERT( INT, 'test') IS NULL
        THEN 'Cast failed'
        ELSE 'Cast succeeded'
    END AS Result;
Code language: SQL (Structured Query Language) (sql)

Here is the result.

Result
-----------
Cast failed

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

B) TRY_CONVERT() raises an error example

The following example returns an error because the integer cannot be cast into an XML data type:

SELECT 
    TRY_CONVERT( XML, 20);
Code language: SQL (Structured Query Language) (sql)

Here is the error:

Explicit conversion from data type int to xml is not allowed.
Code language: SQL (Structured Query Language) (sql)

C) Using TRY_CONVERT() function to convert string to decimal examples

This example uses the TRY_CONVERT() function to convert a string to decimal:

SELECT 
    TRY_CONVERT(DECIMAL(4,2), '12.34');
Code language: SQL (Structured Query Language) (sql)

Here is the result:

Result
----------
12.34
Code language: SQL (Structured Query Language) (sql)

The following shows another example:

SELECT 
    TRY_CONVERT(DECIMAL(4,2), '12.345')  Result;
Code language: SQL (Structured Query Language) (sql)

The result is rounded:

Result
-----------
12.35

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

The following example returns NULL because the TRY_CONVERT() function cannot convert the string '1234.5' to a DECIMAL(4, 2):

SELECT 
    TRY_CONVERT( DECIMAL(4, 2), '1234.5') Result;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

Result
-----------
NULL

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

D) Using TRY_CONVERT() function to convert string to integer examples

This example uses the TRY_CONVERT() function to convert a string to an integer:

SELECT 
    TRY_CONVERT( INT, '100') Result;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

Result
-----------
100

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

This example, on the other hand, returns NULL because the cast fails:

SELECT 
    TRY_CONVERT( INT, '100.5') Result;
Code language: SQL (Structured Query Language) (sql)

Output:

Result
-----------
NULL

(1 row affected)        
Code language: SQL (Structured Query Language) (sql)

E) Using TRY_CONVERT() function to convert datetime to date or time example

This example uses the TRY_CONVERT() function to convert the current system date and time to a date value:

SELECT 
	TRY_CONVERT( DATE, GETDATE()) Result;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

Result
----------
2019-04-28

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

Similarly, you can use the TRY_CONVERT() function to convert the current system date and time to a time value:

SELECT 
	TRY_CONVERT( TIME, GETDATE()) Result;
Code language: SQL (Structured Query Language) (sql)

The following shows the output:

Result
----------------
17:10:19.1700000

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the SQL Server TRY_CONVERT() function to convert a value of one type to another.