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 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 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.
Script used in the video:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 |
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 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!
FINDING THE ERROR, HOW DO YOU DELETE ^?