The SQL Server TRY_CONVERT system function is a great alternative to the standard CONVERT function.
We use CONVERT and TRY_CONVERT to change an expression from one data type to another “on the fly“. This begs the question: What’s the difference?
TRY_CONVERT will return NULL if the conversion fails.
Here is a very simple example of using CONVERT to change the string “100” to an integer data type:
Again, we changed the string representation of “100” to the regular integer representation of 100. Easy enough.
But what if the conversion fails? What if an invalid expression is passed, and SQL Server cannot convert the value to an integer? For example, let’s try changing the string “Roosevelt” to an integer…
Shockingly, we get an error message. Error messages are never a good thing when it comes to software development.
The TRY_CONVERT alternative will return NULL if the conversion fails instead of generating an ugly error message:
Very cool! We could even put this into an ISNULL function to return something like 0 if the conversion fails:
Dealing with a return value of NULL is much easier than dealing with an error message!
Next Steps:
Leave a comment if you found this quick tutorial helpful!
The TRY_CONVERT function can also be found in the full beginner-friendly tutorial for CAST and CONVERT. Check it out here:
CAST and CONVERT: A How-To Guide
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, please leave a comment. Or better yet, send me an email!
