Conversion Functions – SQL Server Denali

Microsoft released 3 new conversion function in SQL Server Denali. In which two functions will tell you whether the action is possible the third one will give you the result. Below are the three  conversion functions

• Try_Convert
• Parse
• Try_Parse

Let’s populate a table with some set of values to test these 3 functions

USE tempdb
GO
CREATE TABLE empdetails (Name VARCHAR(30), Date_of_Joining DATE, Age INT)
INSERT empdetails VALUES('Jithesh','2006-11-30',22)
,('Henry',CONVERT(DATE,'1999-06-11'),40),('Shahul','2009-09-23',27)

TRY_CONVERT

This function will let you know whether the data can be converted to the destination data type. If the conversion is possible then it will return the data else it will return NULL value

SELECT TRY_CONVERT(DATEtime,Name) AS 'Invalid Conversion'
,TRY_CONVERT(VARCHAR(20),Date_of_Joining) AS 'Valid Conversion'
FROM tempdb..empdetails

PARSE

This is another function which will do the conversion for us. You can actually parse the data  and convert it as required, if not possible it will throw error. Here you have one more parameter , that’s nothing but the culture. Use PARSE function to convert only string to datetime and integer, for other conversion use CAST or CONVERT. Syntax is below

PARSE ( string_value AS data_type [ USING culture ] )

--Converting the input to string without culture
SELECT PARSE(CONVERT(CHAR(2),Age) AS MONEY) from tempdb..empdetails
WHERE Name='Jithesh'
--Passing INT as input
SELECT PARSE(Age AS Money) from tempdb..empdetails
WHERE Name='Jithesh'

TRY_PARSE

This function is similar to PARSE however it will return NULL value if the conversion is not possible, in case of PARSE it will throw you error. Use TRY_PARSE to convert string to datetime and number types. This means that these functions will accept input only as string and not as any other data type, that’s why I’m converting the input data as CHAR in example.

SELECT TRY_PARSE(CONVERT(CHAR(2),Age) AS MONEY) 'Valid Conversion'
,TRY_PARSE(CONVERT(CHAR(2),Age) AS DATETIME2) 'Invalid Conversion'
FROM empdetails
WHERE Name='Jithesh'

If you could notice from the example, conversion to money gets succeeded as the input data is valid however when I tried to convert to datetime2 it fails hence it returns NULL value, this is because the input data is not a valid datetime value. If we do the same conversion using PARSE it will throw error. From this examples it’s clear that you need to pass the input as string only and the data should be valid for conversion.

TRY_CONVERT & TRY_PARSE functions helps us to perform the conversion better, in earlier version if the conversion is not possible it will end throwing the error now it’s better that it will return NULL value when the conversion is not possible.