Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
We will examine TRY_CAST and TRY_CONVERT functions in this article. You can find more informartion about CAST and CONVERT functions in the article below.
“CAST and CONVERT Functions in SQL Server(TSQL)”
The TRY_CAST function executes the CAST function. If the CAST function is completed successfully, it returns the result, otherwise it returns NULL. If it is not completed successfully, we can use “case when” to return different values than null when it fails.
The TRY_CONVERT function executes the CONVERT function. If the CONVERT function is completed successfully, it returns the result, otherwise it returns NULL. If it is not completed successfully, we can use “case when” to return different values than null when it fails.
Let’s make examples for a better understanding of these functions.
Examples:
TRY_CAST with successful conversion:
1 | SELECT TRY_CAST('123' AS INT) AS Successful_Conversion; |
TRY_CAST with failed conversion:
1 | SELECT TRY_CAST('123A' AS INT) AS Failed_Conversion; |
TRY_CAST with CASE WHEN Statement:
1 2 3 4 5 | SET DATEFORMAT dmy; SELECT CASE WHEN TRY_CAST('31/01/2018' AS datetime) IS NULL THEN 'Failed Conversion' ELSE 'Successful Conversion' END AS Result; |
TRY_CONVERT with successful conversion:
1 | SELECT TRY_CONVERT(INT,'123') AS Successful_Conversion; |
TRY_CONVERT with failed conversion:
1 | SELECT TRY_CONVERT(INT,'123a') AS Successful_Conversion; |
TRY_CONVERT with CASE WHEN Statement:
1 2 3 4 5 | SET DATEFORMAT dmy; SELECT CASE WHEN TRY_CONVERT(datetime,'31/01/2018') IS NULL THEN 'Failed Conversion' ELSE 'Successful Conversion' END AS Result; |
