TRY Counterparts for CAST, CONVERT and PARSE

For implementing explicit conversion from one type to another, we have been using either CAST or CONVERT functions given by Microsoft SQL Server. CAST is the standard ANSI way of converting and CONVERT is proprietary to Microsoft for converting. SQL Server 2012 introduces another function for converting string to date, time, and number, that accepts an additional argument for culture setting.

In addition to these three functions, CAST, CONVERT and PARSE have their counterparts called TRY_CAST, TRY_CONVERT, and TRY_PARSE. The different between them is, new functions return NULL for conversions with inconvertible input types instead of throwing errors.

Here is an example code;

1. DECLARE @input varchar(10) = '1000'
2.  
3. -- these three SELECTs
4. -- return values without
5. -- any issue
6. SELECT CAST(@input AS money)
7. SELECT CONVERT(money, @input)
8. SELECT PARSE(@input as money)
9.  
10. -- change the value
11. SET @input = 'Thousand'
12.  
13. -- these three SELECTs
14. -- throw errors
15. SELECT CAST(@input AS money)
16. SELECT CONVERT(money, @input)
17. SELECT PARSE(@input as money)
18.  
19. -- these three SELECTs
20. -- return NULLs
21. SELECT TRY_CAST(@input AS money)
22. SELECT TRY_CONVERT(money, @input)
23. SELECT TRY_PARSE(@input as money)

What should we use? It depends on the way you need to handle it. If you need to continue the execution without any error, TRY_* is the best. However if the execution should be stopped with inconvertible data types, traditional conversion would be the best.