SQL TRY CAST

The SQL TRY CAST function or TRY_CAST is one of the Conversions functions, which is similar to the CAST Function. It is used to convert an expression from one data type to another. If it succeeds, then TRY CAST will return the expression in the desired data type. Otherwise, it will return null.

If the TRY_CAST function is unable to convert an expression into the desired data type, then this function will return NULL. If there is no possibility to convert to the required data type explicitly, then it will throw an error. For this TRY CAST demo, we are going to use the Employee table from the Tutorial database.

Employee Table

SQL TRY CAST Function Syntax

The syntax of the TRY_CAST Function is

TRY_CAST (Expression AS Data_Type [(Length)])

-- For example
SELECT CAST (245 AS VARCHAR(50)) AS [result_name]
FROM [Source]
  • Expression: Specify any valid expression that you wish to convert.
  • Data_Type: Specify the Data Type to which you want to convert the expression
  • Length: It is an optional parameter of integer type. You can use this parameter to specify the length of the target SQL Server data type.

SQL TRY CAST Function Example 1

The TRY_CAST Function helps you convert any expression to the desired data type. The query below will show multiple ways to use this function.

DECLARE @str AS VARCHAR(50)
SET @str = '356789'
 
SELECT TRY_CAST(@str AS MONEY) AS Result; 
 
-- Direct Inputs
SELECT TRY_CAST('2345' AS DECIMAL(10, 2)) AS Result; 
 
SELECT TRY_CAST(123.456 AS VARCHAR(50)) AS Result; 
 
SELECT TRY_CAST('09/19/2017' AS DATETIME) AS Result;  
 
SELECT TRY_CAST('09/14/2017' AS DATETIME2) AS Result;
 
SELECT TRY_CAST(GETDATE() AS VARCHAR(30)) AS Result;

As you can see, it is returning the same result as we got in the Cast function. We have already explained the program flow in the CAST article so, please refer to the same.

SQL TRY CAST 2

TRY CAST Function Example 2

In this example, we will show you, What will happen when we pass wrong expressions and NULL values to the Try cast function.

SELECT TRY_CAST('19/14/2017' AS DATETIME2) AS Result;

SELECT TRY_CAST('Tutorial Gateway' AS INT) AS Result;

--Let me try the function with NULL value
SELECT TRY_CAST(NULL AS INT) AS Result;

From the below screenshot, you can see it is returning NULL for the expressions. Because it is unable to convert them to the required data type.

TRY CAST Function 3

Let us use what will happen if we try to convert the 65 (integer value) to the XML data type. As you know, it is not possible to convert an integer to XML implicitly or explicitly. So, this function is returning Error as output.

SELECT TRY_CAST(65 AS XML) AS Result;

Execute the Try_cast query

Messages
-------
Msg 529, Level 16, State 2, Line 2
Explicit conversion from data type int to xml is not allowed.

SQL TRY CAST (TRY_CAST) Function Example 3

In this example, we will apply the TRY_CAST function on our Employee table.

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,TRY_CAST([YearlyIncome] AS DECIMAL(10, 3)) AS [Yearly Income]
      ,TRY_CAST([Sales] AS INT) AS [Sales Amount]
      ,TRY_CAST([HireDate] AS DATETIME2) AS [Hire Date]
  FROM [Employee]
TRY CAST Function 5
Categories SQL