Hey guys!

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

Who has never come across an error message like this?

Msg 245, Level 16, State 1, Line 40
Conversion failed when converting the 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 version 2005 or 2008 of SQL Server, you can use the functions ISNUMERIC (whole numbers, reals, currencies, decimals, etc.) and ISDATE (dates) to perform validation 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 and date types only), TRY_CAST (any type) or TRY_CONVERT (any type, with 3 optional parameters to control input mask) to perform data conversion, the way you normally use it. The difference between these functions is that in case of inconsistency during the 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, that is, which would generate an error when attempting a traditional conversion, using CAST/CONVERT.

Since SQL Server 2005, it is also possible to 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.

Script used in the video:

IF ( OBJECT_ID ( 'tempdb..#Teste' ) IS NOT NULL ) DROP TABLE #Teste
SELECT
    CAST ( BusinessEntityID AS VARCHAR ( MAX ) ) AS BusinessEntityID ,
    Title ,
    FirstName ,
    LastName ,
    rowguid ,
    CONVERT ( VARCHAR ( MAX ) , ModifiedDate , 112 ) AS ModifiedDate
INTO
    #Teste
FROM
    AdventureWorks2019.Person.Person


-- Simulando alguns erros
UPDATE #Teste
SET
    ModifiedDate = LEFT ( ModifiedDate , 7 ) + '2'
WHERE
    BusinessEntityID BETWEEN 1 AND 20
    OR BusinessEntityID BETWEEN 5000 AND 6000


UPDATE #Teste
SET
    BusinessEntityID += 'D'
WHERE
    BusinessEntityID BETWEEN 1400 AND 1450


SELECT *
FROM #Teste


SELECT *
FROM #Teste
WHERE BusinessEntityID LIKE '%D'
OR BusinessEntityID BETWEEN 1 AND 50
OR BusinessEntityID BETWEEN 5000 AND 6000



SELECT CONVERT ( INT , BusinessEntityID )
FROM #Teste


SELECT CONVERT ( DATE , ModifiedDate , 112 )
FROM #Teste


SELECT BusinessEntityID
FROM #Teste
WHERE TRY_CAST ( BusinessEntityID AS INT ) IS NULL


SELECT ModifiedDate
FROM #Teste
WHERE TRY_CONVERT ( DATE , ModifiedDate , 112 ) IS NULL


SELECT ModifiedDate , TRY_CONVERT ( DATE , ModifiedDate , 112 )
FROM #Teste


SELECT * 
FROM #Teste
WHERE ISNUMERIC ( BusinessEntityID ) = 0


SELECT * FROM #Teste
WHERE ISDATE ( ModifiedDate ) = 0

Well guys, I hope you enjoyed this simple, small but very objective article that helps a lot for those just starting out on their data journey.
A big hug and see you next time!