You are here:Home » tsql » Intresting Conversion functions arrived in SQL Server Denali CTP3

Intresting Conversion functions arrived in SQL Server Denali CTP3

Last time, I have posted for the new Analytical functions of Denali CTP3.You can see WHAT IS NEW in SQL Server Denali CTP3. This time I am going to write for new conversion functions which are summarized below,

1. TRY_CONVERT() - Returns a value to be cast to the specified data type if the cast succeeds; otherwise, returns null.
2. PARSE() - Returns the result of an expression, translated to the requested data type.
3. TRY_PARSE() - Returns the result of an expression, translated to the requested data type if succeeds and return null if fails.

Let us understand them in brief with examples. First i would like to start with TRY_CONVERT(), It has require three arguments, first is data_type(length) in which value cast, second is expression value and third style which is optional.

Examples:
SELECT TRY_CONVERT(FLOAT,'test') 

SELECT TRY_CONVERT(CHAR(2),'test') 

SELECT TRY_CONVERT(DATETIME,'4/5/2011') 

SELECT TRY_CONVERT(DATETIME2,'4-5-2011') 

SELECT TRY_CONVERT(VARCHAR(12),'4-5-2011') 

SELECT TRY_CONVERT(TIME,'115056') 

SELECT TRY_CONVERT(TIME,'4-5-2011') 

SELECT 
    CASE WHEN TRY_CONVERT(BIGINT,'2011-06-09') IS NULL 
    THEN 'Failed'
    ELSE 'Succeeded'
END

SELECT IIF (TRY_CONVERT(DECIMAL(10,2),'22.5') IS NULL,'Failed','Succeeded' )
GO

Now we will see next conversion function PARSE () which has require three parameters, first is string_value which allows to enter nvarchar (4000) value and must be a valid representation for requested datatype otherwise PARSE will raise an error. Second is data_type which representing the data type requested for the result and the third is a culture which is optional and take a current language of session as the default.

But here are some limitation of the data type which can used for requesting result. In the first example, it parses the string value to datetime and begin dataype and in the second example, parsing a string value to numeric and datetime datatype and you can see there resulted output also.
SELECT PARSE('Monday, 13 December 2010' AS DATETIME) 

SELECT PARSE('Monday, 13 December 2010' AS BIGINT) 

SELECT PARSE('Monday, 13 December 2010' AS TIME) 

SELECT PARSE('18' AS TIME) 

SELECT PARSE('18' AS NUMERIC(18,2)) 

SELECT PARSE('18' AS DATETIME) 
GO

At last we will review TRY_PARSE () which is same as PARSE () but TRY_PARSE () returns the result of an expression, translated into the requested data type if succeeds and return null if fails. It requires same arguments as PARSE (). You can see the resulted output with both of the function here. We will test TRY_PARSE () and PARSE () function with same parameters and compare output as well.
SELECT PARSE('15' AS datetime)
SELECT TRY_PARSE('15' AS datetime2) 

SELECT PARSE('4/4/2011' AS datetime2) 
SELECT TRY_PARSE('4/4/2011' AS datetime2) 

SELECT PARSE('18.2' AS BIGINT)
SELECT TRY_PARSE('18.2' AS BIGINT) 

SELECT PARSE('18' AS NUMERIC(18,2)) 
SELECT TRY_PARSE('18' AS NUMERIC(18,2)) 
GO

See something you want to share?