Tutorial Gateway

SQL TRY CAST

by

The SQL TRY CAST is one of the SQL Conversions Function which is similar to CAST Function. It is used to convert an expression from one data type to another. If it succeed then it will return the expression in desired data type otherwise, it will return null. In this article we will show you, How to use, or write SQL Server TRY CAST with example. For this we are going to use the Employee table from SQL Tutorial database.

SQL TRY CAST 1

SQL TRY CAST Function Syntax

The basic syntax of the SQL Server TRY_CAST Function is as shown below:

-- 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 data type.

TIP: If the TRY_CAST function is unable to convert an expression into desired data type then this function will return NULL. If there is no possibility to explicitly convert to desired data type then it will throw an error.

SQL TRY CAST Function Example 1

The SQL Server TRY_CAST Function help you to convert any expression to desired data type. The below shown query will show multiple ways to use this function.

T-SQL CODE

-- 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;

OUTPUT

As you can see, it is returning the same result as we got in the Cast function. We are already explained the program flow in 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.

T-SQL CODE

-- 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;

OUTPUT

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

SQL TRY CAST 3

Let us use what will happen, if we tried to convert the 65 integer to XML data type. As you know it is not possible to convert 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;

OUTPUT

SQL TRY CAST 4

SQL TRY CAST Function Example 3

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

T-SQL CODE

-- 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]

OUTPUT

SQL TRY CAST 5

Thank You for Visiting Our Blog

Placed Under: SQL