SQL Server TRY_CAST Function

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

SQL Server TRY_CAST() function overview

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

The following shows the syntax of the TRY_CAST() function:

TRY_CAST ( expression AS data_type [ ( length ) ] )  
Code language: SQL (Structured Query Language) (sql)

The TRY_CAST() accepts two arguments:

• data_type is any valid data type into which the function will cast the expression.
• expression is the value to cast.

The TRY_CAST() function takes the input value and tries to cast it to a value of the specified data type. It returns the value in the specified data if the cast succeeds; Otherwise, it returns NULL. But, if you request a conversion that is explicitly not allowed, the TRY_CAST() function will fail with an error.

TRY_CAST() vs. CAST()

If the cast fails, the TRY_CAST() function returns NULL while the CAST() function raises an error.

You use the NULL handling functions or expressions such as ISNULL(), COALESCE, or CASE to handle the result of the TRY_CAST() function in case the cast fails. On the other hand, you use the TRY...CATCH statement to handle the result of the CAST() function if the cast fails.

SQL Server TRY_CAST() function examples

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

A) TRY_CAST() returns NULL example

The following example shows how the TRY_CAST() function returns NULL when the cast fails:

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

Here is the output.

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

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

B)TRY_CAST() raises an error example

This example returns an error because a number cannot be cast into an XML data type:

SELECT 
    TRY_CAST(30.5 AS XML);
Code language: SQL (Structured Query Language) (sql)

Here is the error:

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

C) Using TRY_CAST() function to CAST string to decimal examples

The following example uses the TRY_CAST() function to convert a string to a decimal:

SELECT 
    TRY_CAST('12.34' AS DECIMAL(4, 2)) Result
Code language: SQL (Structured Query Language) (sql)

Here is the result:

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

Here is another example:

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

The result is rounded up to 12.35:

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

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

This example returns NULL because the TRY_CAST() function cannot cast the string '1234.5' to a DECIMAL(4, 2):

SELECT 
    TRY_CAST('1234.5' AS DECIMAL(4, 2)) 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_CAST() function to convert string to integer examples

The following example uses the TRY_CAST() function to convert a string to an integer:

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

Here is the result set:

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

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

This example returns NULL because the cast fails:

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

Here is the output:

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

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

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

The following example uses the TRY_CAST() function to convert the current system date and time to a date value:

SELECT 
	TRY_CAST(GETDATE() AS DATE) 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)

Likewise, you can use the TRY_CAST() funtion to convert the current system date and time to a time value:

SELECT 
	TRY_CAST(GETDATE() AS TIME) Result;
Code language: SQL (Structured Query Language) (sql)

The following shows the output:

Result
----------------
17:36:37.5900000

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

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