SQL Server 2012 - TRY_CONVERT( ) -Data type conversion function

TRY_CONVERT function tries to convert the values passed to it ,into a specified data type.

 If the conversion is successful then it will return the value of the specified data type,

 else it will return a NULL value.

Syntax :

TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )

data_type [ ( length ) ] - The data type into which to cast expression.

                          

expression - The value to be cast         

style  -    Optional integer expression that specifies how the TRY_CONVERT function is to translate expression.

Examples for TRY_CONVERT( ):

 USE AdventureWorks2012

--using TRY_CONVERT() , to convert integer to character 
 
SELECT TRY_CONVERT(NVARCHAR(1),1)
GO

--if the conversion fails , CONVERT ( ) will return error, 

TRY_CONVERT( ) will return NULL

SELECT CONVERT(INT,Gender) FROM [HumanResources].[Employee] WHERE BusinessEntityID = 1

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'M' to data type int.

SELECT TRY_CONVERT(INT,Gender) FROM [HumanResources].[Employee] WHERE BusinessEntityID = 1

--sufficient data type length is not specified ,CONVERT ( ) will return error, TRY_CONVERT( ) will return NULL

SELECT CONVERT(varchar(100),Instructions) FROM [Production].[ProductModel]  WHERE ProductModelID = 7

Msg 6354, Level 16, State 10, Line 1
Target string size is too small to represent the XML instance

SELECT TRY_CONVERT(varchar(100),Instructions) FROM [Production].[ProductModel]  WHERE ProductModelID = 7

--third argument in TRY_CONVERT( ) i.e ., style accepts the same values as the style parameter of the CONVERT ( ) function.

SELECT TRY_CONVERT(NVARCHAR(30),getdate(),107)
SELECT TRY_CONVERT(NVARCHAR(30),getdate(),104) 


--TRY_CONVERT returns an error when the cast is explicitly not permitted.


SELECT TRY_CONVERT(xml, 10) AS Result;
GO

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

Reference - http://msdn.microsoft.com/en-us/library/hh230993.aspx