SQL TRY CAST 5

SQL TRY CAST

by

The SQL TRY CAST function is one of the SQL Conversions Function, 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 SQL 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 SQL Server TRY CAST demo, we are going to use the Employee table from the SQL Tutorial database.

SQL TRY CAST 1

SQL TRY CAST Function Syntax

The syntax of the SQL Server TRY_CAST Function is

-- SQL SERVER TRY_CAST Syntax
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 SQL Server TRY_CAST Function helps you to convert any expression to the desired data type. The below-shown query will show multiple ways to use this function.

-- SQL SERVER TRY_CAST FUNCTION Example 
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 SQL CAST article so, please refer the same.

SQL TRY CAST 2

SQL TRY CAST Function Example 2

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

-- SQL SERVER TRY_CAST FUNCTION Example 
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.

SQL TRY CAST 3

Let us use what will happen if we tried 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.

-- SQL SERVER TRY_CAST FUNCTION Example 
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 Function Example 3

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

-- SQL SERVER TRY_CAST EXAMPLE
USE [SQL Tutorial]
GO
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]
SQL TRY CAST 5