Arithmetic overflow error converting varchar to data type numeric?

Question

Just now I was getting this error when running a stored procedure:

Arithmetic overflow error converting varchar to data type numeric.

I located the line where that error was coming from and this is the code on that line:

SELECT @AF_MIN_3L = LEFT(MIN([A-F Est_CY]), 6) - 0.000001 FROM #Ent_AF_3

Earlier in the stored procedure, I declared @AF_MIN_3L as data type FLOAT, created the temp table #Ent_AF_3 and in doing so, made the column [A-F Est_CY] data type FLOAT. Is the following code creating a non-FLOAT value?

LEFT(MIN([A-F Est_CY]), 6) - 0.000001

I hope it's a simple casting issue and all I have to do is something like this:

LEFT(MIN(CAST([A-F Est_CY] AS FLOAT)), 6) - CAST(0.000001 AS FLOAT)

I didn't want to run the whole procedure again without being sure I fixed the issue. Thanks for any help.

I think you need to cast it back to float before the subtraction: SELECT @AF_MIN_3L = CAST(LEFT(MIN([A-F Est_CY]), 6) AS float ) - 0.000001 FROM #Ent_AF_3 but I guess it depends on what it is you actually want to accomplish. — jpw
It seems like your operations are out of order - shouldn't that be MIN(CAST(LEFT([A-F Est_CY], 6) AS FLOAT))? Take the left 6 characters, convert to float, and find the minimum? — D Stanley
What's your goal e.g. select @a=-0.00001 leads to select @a > -1E-05 and select LEFT(@a, 6) > -1e-00 — bummi

HoneyBadger HoneyBadger · Accepted Answer · 2014-09-15T14:24:59

If you use a string function, which is what LEFT is, it resturns a string value. As described here, the return datatype of the LEFT function does indeed return a VARCHAR or NVARCHAR. So, ideed, a CAST or CONVERT back to FLOAT is required. You should of course convert back to FLOAT AFTER the LEFT function, so it would be: CAST(LEFT(...) as FLOAT).

Arithmetic overflow error converting varchar to data type numeric?

2 Answers