SQL TRY CONVERT

The SQL TRY CONVERT conversions Function is similar to the CONVERT Function. It is used to convert an expression from one data type to another. If it succeeds, then it will return the expression in the required data type. Otherwise, Try convert will return null.

If the SQL TRY CONVERT function is unable to convert an expression into the desired data type, then it returns NULL. If there is no possibility to convert to the required datatype explicitly, then it will throw an error. For this SQL Server Try Convert, we are going to use the Employee table from the database.

SQL TRY CONVERT EXAMPLE 0

SQL TRY CONVERT Function Syntax

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

TRY_CONVERT (Data_Type [(Length)], Expression, [Style])

-- For example
SELECT TRY_CONVERT (VARCHAR(50), 245) AS [result_name]
FROM [Source]
  • Data_Type: Specify the Data Type to which you want to convert an expression
  • Length: It is an optional parameter of integer type. Use this parameter to specify the target datatype length.
  • Expression: Please specify any valid expression that you want to convert.
  • Style: It is also optional of integer type. Use this SQL Server parameter to define the style.

SQL TRY CONVERT Example 1

The SQL Server TRY_CONVERT Function helps you to convert any expression to the desired data type. The below-shown query will show multiple ways to use this Try Convert function.

-- SQL SERVER TRY_CONVERT Example 
DECLARE @str AS VARCHAR(50)
SET @str = '356789'
 
SELECT TRY_CONVERT(MONEY, @str) AS Result; 
 
-- Direct Inputs
SELECT TRY_CONVERT(DECIMAL(10, 2), '2345') AS Result; 
 
SELECT TRY_CONVERT(VARCHAR(50), 123.456) AS Result; 
 
SELECT TRY_CONVERT(DATETIME, '09/19/2017') AS Result;  
 
SELECT TRY_CONVERT(VARCHAR(30), GETDATE(), 109) AS Result;
SQL TRY CONVERT 1

TRY CONVERT Function Example 2

In this example, we will show what happens when we pass wrong expressions, and NULL values to Try convert function.

-- SQL SERVER TRY_CONVERT Example 

SELECT TRY_CONVERT(DATETIME, '15/19/2017') AS Result;  
 
SELECT TRY_CONVERT(INT, 'Tutorial Gateway') AS Result;

SELECT TRY_CONVERT(INT, NULL) AS Result;

See the SQL Try convert function is returning NULL for the expressions. Because it is unable to convert them to the desired data type.

SQL TRY CONVERT 2

Let us use what will happen if we tried to convert the 12 integers 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_CONVERT Example 

SELECT TRY_CONVERT(XML, 12) AS Result;

Run the above try_convert query

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

TRY CONVERT Function Example 3

In this example, we will apply the Sql Server TRY_CONVERT function on our Employee table.

-- SQL SERVER TRY_CONVERT Example 
USE [SQL Tutorial]
GO
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,TRY_CONVERT(DECIMAL(10, 3), [YearlyIncome]) AS [Yearly Income]
      ,TRY_CONVERT(VARCHAR(50), [Sales], 1) AS [Sales Amount]
      ,TRY_CONVERT(DATETIME, [HireDate], 109) AS [Hire Date]
  FROM [Employee]
SQL TRY CONVERT 4