SQL Server - How to identify data conversion errors using TRY_CAST, TRY_CONVERT, TRY_PARSE, ISNUMERIC, and ISDATE

Views: 733
Reading Time: 2 minutes

Hey guys!

In this article, I would like to share with you a question you posted a few minutes ago in a Whatsapp group about a very common SQL Server problem, especially in the areas of BI and / or development, which are errors. data type conversion, ie the attempt to convert a value from one type to another, very common when the source data comes from unstructured sources like CSV, JSON and text files and they are all imported as text (varchar / nvarchar) and converted to the most appropriate types later.

Who has never come across such an error message?

Msg 245, Level 16, State 1, Line 40
Conversion failed when converting varchar value '1401D' to data type int.

Msg 241, Level 16, State 1, Line 44
Conversion failed when converting date and / or time from character string.

If you are using SQL Server version 2005 or 2008, you can use the following functions: ISNUMERIC (whole, real, currency, decimal, etc.) and ISDATE (dates) to validate to identify values ​​that are inconsistent when encountering an error in the conversion attempt.

If you are using version 2012 or higher, you can use, in addition to the functions previously seen, the new functions TRY_PARSE (numeric types and date only), TRY_CAST (any type) or TRY_CONVERT (any type, with optional 3 parameter to control input mask) to perform data conversion as you normally use it. The difference of these functions is that in case of inconsistency during conversion, the function will not generate an error / exception, on the contrary, it will silently ignore this error (returning NULL for cases that are inconsistent). This can be used both when displaying results and in WHERE to help identify which records returned NULL, ie which would generate an error when attempting a traditional conversion using CAST / CONVERT.

Since SQL Server 2005, you can also validate this information using Regular Expressions (Regexp), as I demonstrated with several examples and explanations in the article. SQL Server - How to use regular expressions (RegExp) in your database.

Well guys, I hope you enjoyed this simple article, small but very objective and that helps a lot who is starting the journey in the data area.
A big hug and see you next time!