What is the difference between CONVERT() and TRY_CONVERT() function in SQL Server?

Posted by Bandi on 9/30/2013 | Category: Sql Server Interview questions | Views: 6757 | Points: 40

Post |

Interview FAQs |

Exclusive Questions |

Interview Home

Answer:

The new TRY_CONVERT function is very similar to the CONVERT function except that it returns NULL when the conversion cannot be completed, such as attempting to put alphabetic characters into a numeric data type. If the conversion cannot be completed because the data type of the expression is not allowed to be explicitly converted to the specified data type, an error will be thrown.

The format for the TRY_CONVERT is this:

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

Notice that you have the option of specifying a style as you could with the CONVERT function.

An example of its use could be to try a conversion from a string input into a datetime data type where a NULL return means the user did not provide the proper input.

DECLARE @stringparm VARCHAR(30),



@result VARCHAR(30)



SET @stringparm = '2/30/2012'



SELECT @result = CASE WHEN TRY_CONVERT (datetime, @stringparm)



IS NULL THEN 'Bad Date'



ELSE 'Good Date'



END



SELECT @result



Result:



-- -- -- -- -- -- -- -- -- -- 



Bad Date

Asked In: Many Interviews | Alert Moderator

Bookmark It

< Previous : How do I insert the results of the stored procedur ...

Next > : Which of the following clause makes a query cost-e ...

Comments or Responses

Posted by: Samirbhogayta on: 10/15/2013 | Points: 10

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

More Interview Questions by Bandi

Latest Interview Questions