All TRY_CAST( ), TRY_CONVERT( ) and TRY_PARSE( ) enable us to detect and potential errors and prepare error handling for the SQL script so that we could prevent an expected failures.
TRY_CAST() returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.
DECLARE @dtSQL DATETIME = '09/13/2013'; SELECT TRY_CAST( @dtSQL AS VARCHAR(12)) AS[TRY_CAST Result]; GO |
TRY_CONVERT() returns a value cast to the specified data type if the cast succeeds; otherwise, returns null. It can be remoted to servers that have a version of SQL Server 2012 and above.
TRY_CONVERT is a new keyword in compatibility level 110 whereas TRY_CAST is not a new reserved keyword and is available in all compatibility levels.
TRY_CONVERT supports additional feature of styling unlike TRY_CAST. The next example demonstrates we could format a given date to different formats.
DECLARE @dtSQL DATETIME = '09/13/2013'; SELECT TRY_CONVERT(VARCHAR(10), @dtSQL, 111) AS [TRY_CONVERT YYYY/MM/DD Result], TRY_CONVERT(VARCHAR(10), @dtSQL, 104) AS [TRY_CONVERT DD.MM.YYYY Result]; GO |
You may use this online table for a quick reference to SQL Server date formatting.
Both TRY_CAST and TRY_CONVERT return an error when the cast is explicitly not permitted. Refer this table to see acceptable data type conversions.
SELECT TRY_CONVERT(text, 4) AS [TRY_CONVERT Result]; GO SELECT TRY_CAST(4 AS hierarchyid) AS [TRY_CAST Result]; GO |
Both TRY_CAST and TRY_CONVERT have the same semantics when connecting to remote servers. TRY_CONVERT is not supported on current SQL Azure.
TRY_PARSE() returns the result of an expression, translated to the requested data type, or null if the cast fails in SQL Server 2012 later versions.
DECLARE @dtSQL VARCHAR(10) = '09-2013-13'; SELECT TRY_CAST(@dtSQL AS datetime2) AS [TRY_CAST Result], TRY_CONVERT(datetime2, @dtSQL) AS [TRY_CONVERT Result], TRY_PARSE(@dtSQL AS datetime2) AS [TRY_PARSE Result]; GO |
According to SQL Server online book TRY_PARSE should be used only for converting from string to date/time and number types. For general type conversions, continue to use CAST or CONVERT.
There is a performance overhead in parsing the string value with TRY_PARSE and care should be taken.
TRY_PARSE relies on the presence of .the .NET Framework Common Language Runtime (CLR). Do not use it for functions that require the CLR would cause an error on the remote server.
Read More
http://technet.microsoft.com/en-us/library/hh974669.aspx
http://technet.microsoft.com/en-us/library/hh230993.aspx
http://technet.microsoft.com/en-us/library/hh213126.aspx