TRY_CONVERT CONVERSION FUNCTION IN SQL SERVER 2012

TRY_CONVERT CONVERSION FUNCTION IN SQL SERVER 2012

TRY_CONVERT is one of the new built-in conversion function introduced as a Part of Sql Server 2012. TRY_CONVERT function is similar to the CONVERT  function, but if CONVERT function fails to convert the value to the requested type then throws an exception where as TRY_CONVERT function returns NULL value.

Note:  TRY_CONVERT function raises an exception if we try to an convert expression to a type which is not explicitly permitted.

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

Parameter Details:

data_type     : The data type into which to cast expression.
expression  : The value to be cast.
style               : Optional integer value that specifies how the TRY_CONVERT function is to translate expression. The Values for this integer parameter are same as the one used by CONVERT function.

Now let us understand this TRY_CONVERT function with examples:

TRY_CONVERT – Succeess

1 2 3 4 5 6

SELECT TRY_CONVERT(INT, '100') SELECT TRY_CONVERT(NUMERIC(8,2), '1000.06') SELECT TRY_CONVERT(INT, 100) SELECT TRY_CONVERT(NUMERIC(8,2), 1000.06) SELECT TRY_CONVERT(DATETIME, '05/18/2013') SELECT TRY_CONVERT(DATETIME, '05/18/2013',111)

RESULT:

TRY_CONVERT – Fails

In the below example we are trying to convert an invalid date i.e. 31st Feburary, 2013. In such cases TRY_CONVERT function will return NULL value instead of throwing exception.

1 2	SELECT TRY_CONVERT(DATETIME, '02/31/2013')             AS 'TRY_CONVERT Function Fails'

Result:
TRY_CONVERT Function Fails
————————–
NULL

TRY_CONVERT – Throws Exception

In the below example we are trying to convert an integer value to XML type which is not permitted explicitly, in such cases TRY_CONVERT function raises an exception.

SELECT TRY_CONVERT(XML, 10)

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

But on the other-hand conversion of an string value to an XML type is supported. Let us see this with an example:

1	SELECT TRY_CONVERT(XML, '10') AS 'XML Output'

Result:

XML Output
——————————-
10

Difference Between CONVERT and TRY_CONVERT

Both CONVERT and TRY_CONVERT function converts the expression to the requested type. But if the CONVERT function fails to convert the value to the requested type then raises an exception, on the other hand if TRY_CONVERT function returns a NULL value if it fails to convert the value to the requested type. Below example demonstrates this difference

1 2 3 4

SELECT CONVERT(DATETIME, '02/31/2013')             AS 'CONVRT Function Result' SELECT TRY_CONVERT(DATETIME, '02/31/2013')             AS 'TRY_CONVERT Function Result'

Result:
CONVRT Function Result
———————–
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

TRY_CONVERT Function Result
————————-
NULL

Below example demonstrate how we can check the result of TRY_CONVERT function in IF condition:

1 2 3 4

IF TRY_CONVERT(DATETIME,'Basavaraj') IS NULL         PRINT 'TRY_CONVERT: Conversion Successful' ELSE         PRINT 'TRY_CONVERT: Conversion Unsuccessful'

Result:
TRY_CONVERT: Conversion Successful