Skip to main content
TRY_CAST (Transact-SQL)

Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.

Applies to: SQL Server (SQL Server 2012 through current version) , Azure SQL Database.

Topic link icon Transact-SQL Syntax Conventions

Syntax
TRY_CAST ( expression AS data_type [ ( length ) ] )
Arguments
expression

The value to be cast. Any valid expression.

data_type

The data type into which to cast expression.

length

Optional integer that specifies the length of the target data type.

The range of acceptable values is determined by the value of data_type.

Return Types

Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.

Remarks

TRY_CAST takes the value passed to it and tries to convert it to the specified data_type. If the cast succeeds, TRY_CAST returns the value as the specified data_type; if an error occurs, null is returned. However if you request a conversion that is explicitly not permitted, then TRY_CAST fails with an error.

TRY_CAST is not a new reserved keyword and is available in all compatibility levels. TRY_CAST has the same semantics as TRY_CONVERT when connecting to remote servers.

Examples

A. TRY_CAST returns null

The following example demonstrates that TRY_CAST returns null when the cast fails.

SELECT 
    CASE WHEN TRY_CAST('test' AS float) IS NULL 
    THEN 'Cast failed'
    ELSE 'Cast succeeded'
END AS Result;
GO

Here is the result set.

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

(1 row(s) affected)

The following example demonstrates that the expression must be in the expected format.

SET DATEFORMAT dmy;
SELECT TRY_CAST('12/31/2010' AS datetime2) AS Result;
GO

Here is the result set.

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

(1 row(s) affected)

B. TRY_CAST fails with an error

The following example demonstrates that TRY_CAST returns an error when the cast is explicitly not permitted.

SELECT TRY_CAST(4 AS xml) AS Result;
GO

The result of this statement is an error, because an integer cannot be cast into an xml data type.

Explicit conversion from data type int to xml is not allowed.

C. TRY_CAST succeeds

This example demonstrates that the expression must be in the expected format.

SET DATEFORMAT mdy;
SELECT TRY_CAST('12/31/2010' AS datetime2) AS Result;
GO

Here is the result set.

Result
----------------------------------
2010-12-31 00:00:00.0000000

(1 row(s) affected)
Microsoft is conducting an online survey to understand your opinion of the MSDN Web site. If you choose to participate, the online survey will be presented to you when you leave the MSDN Web site.

Would you like to participate?