TRY_CAST (Transact-SQL)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
SQL analytics endpoint in Microsoft Fabric
Warehouse in Microsoft Fabric
Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.
Transact-SQL syntax conventions
Syntax
TRY_CAST ( expression AS data_type [ ( length ) ] )
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
Arguments
expression
Specifies any valid expression to be cast.
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
isn't 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.
TRY_CAST
doesn't work for varchar(max) if the length is over 8000.
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/2022' 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 can't 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/2022' AS DATETIME2) AS Result;
GO
Here is the result set.
Result
----------------------------------
2022-12-31 00:00:00.0000000
(1 row(s) affected)