CODEWITHSUNDEEP
sqlsql-serversql-server-2012
user3083674
user3083674

Reputation:

Arithmetic overflow error converting varchar to data type numeric?

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.

Upvotes: 1

Views: 17314

Answers (2)

BadGesa
BadGesa

Reputation: 1

The problem is your precision - if you have a float > 0.999999 on the temp file you will get the error because of the implicit conversion.

Use:

SELECT CAST(LEFT(MIN([A-F Est_CY]), 6) AS float) - 0.000001 FROM #Ent_AF_4

Upvotes: 0

HoneyBadger
HoneyBadger

Reputation: 15140

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).

Upvotes: 1

Related Questions