How do I handle a “Error converting data type” error?

A reader recently asked about a error converting data type error they received.  Since this is a common issue, especially when numeric data is stored withing VARCHAR datatypes, I thought you would appreciate the answer I shared with them.

I’m using Windows 10 and SQL SMS 2014.  When I run a query, I get the following error message: 

“Error converting data type varchar to float.”  Can you help in troubleshooting this error message?

Usually when SQL tries to convert a number, such as 1.25 from text to float it can do so easily; however, it does get hung up and will throw an error converting data if there  are some non-numeric characters in the values.

For example, converting 1.25 would work, but converting $1.25 would not, since the server wouldn’t know how to deal with the “$“

My suspicion is that you have some data in the column you’re trying to convert isn’t “purely” numeric.

If you go through the data I be you see some alphabetical data there.

Here are some examples you can try in the query editor:

--EXAMPLES
DECLARE @myText as varchar(20)
DECLARE @myFloat as Money

--This Works!  :)
Print 'Try 1.20 * 1.10'
SET @myText = '1.20'
SET @myFloat = @myText * 1.10
Print @myFloat


--This Will throw an error   :(
PRINT 'Try $1.20 * 1.10'
SET @myText = '$1.20'
SET @myFloat = @myText * 1.10
Print @myFloat

Depending on what you’re trying to do with your data, such as using it in a query, you may have to use an IIF function in conjunction with ISNUMERIC to test if the column value can be converted, and if not, then display another suitable value.

The ISNUMERIC function returns 1 if the value tested is numeric; otherwise 0.  It can be use to test up-front whether characters can be tested to numeric data types.

Here is an example:

SELECT IIF(ISNUMERIC(myTexttoFloatColumn) = 1,
           myTextToFloatColumn*1.50, 
           myTexttoFloatColumn)
FROM   mySampleTable

This would either multiply the coloumn by 1.5, if the column can be converted to a number, or just display the original value…

Hope this helps!