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.

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 change the required datatype explicitly, then it will throw an error. For this Try Convert, we are going to use the Employee table from the database.

EMployee Table 0

SQL TRY CONVERT 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 change 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 change.
  • Style: It is also optional for an integer type. Use this SQL Server parameter to define the style.

SQL TRY CONVERT Example

The 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.

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;
TRY CONVERT Example

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

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 change them to the desired data type.

SQL TRY CONVERT 2

Let us use what will happen if we try to change 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 an Error as output.

SELECT TRY_CONVERT(XML, 12) AS Result;

Run the above query

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

TRY_CONVERT Example

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

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
Categories SQL