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
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)
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
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'
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.