1

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.

Improve this question
3
  • 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. Commented Sep 15, 2014 at 14:20
  • 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? Commented Sep 15, 2014 at 14:21
  • What's your goal e.g. select @a=-0.00001 leads to select @a > -1E-05 and select LEFT(@a, 6) > -1e-00 Commented Sep 15, 2014 at 14:22

2 Answers 2

Reset to default
1

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

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

This worked great, thanks. CAST(LEFT(MIN([A-F Est_CY]), 6) AS FLOAT) - 0.000001 erased the error.
@ultimate8 Declare @a float select @a=-0.00001 select CAST(LEFT(@a, 6) AS FLOAT) - 0.000001 will lead to -1,000001 , I don't think this is what you would like to get?
0

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
Improve this answer

1 Comment

Didn't see this until after I re-ran my procedure but this is exactly what I ended up doing. It ran error free so thank you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.