SQL Server 2014 error: Arithmetic overflow error converting expression to data type int

Question

Can you explain this error in SQL Server 2014 Express?

SELECT FLOOR(21474837 * 100)    -- doesn't work
SELECT FLOOR(214748370 * 10)    -- doesn't work
SELECT FLOOR(2147483700)        -- works
SELECT FLOOR(21474837 / 0.01)   -- works

The number 21474837 is chosen because inferior numbers have no problem neither

S3S · Accepted Answer · 2017-09-14 20:32:13Z

3

That's because FLOOR returns same datatype as the input, which is INT in your case. Add some decimals and it will fix it.

SELECT FLOOR(21474837.00 * 100)

The largest INT number is 2147483647 and 21474837 * 100 = 2147483700, which is 53 too many

answered Sep 14, 2017 at 20:32

S3S

25.2k5 gold badges29 silver badges48 bronze badges

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

7 Comments

steve v Over a year ago

Isn't the root problem how it is handling multiplication and type inference? Won't it have the same error even if you don't call Floor?

S3S Over a year ago

Yes, it would happen if you didn't call floor and didn't invoke an implicit conversion using a decimal on either multiple or the numerator / denominator. But the question was about floor, so floor I specified my answer towards it. I added a nugget regarding this at the bottom

steve v Over a year ago

I guess my point is, the explanation for why there is an error is not described explicitly by this answer. The cause of the error relates to type inference and multiplication behaviors. Multiplying int by int causes the server to assume an int result. A constant value that is too big for an int will cause the server to use a different data type for the representation. Dividing by a decimal will result in a decimal result. The behavior of FLOOR is irrelevant more or less. The solution is correct, but not the explanation.

S3S Over a year ago

Hence The largest INT number is 2147483647 and 21474837 * 100 = 2147483700, which is 53 too many

ainasiart Over a year ago

the solution, I had it, but the reason was strange for me, thanks @scsimon

|

Collectives™ on Stack Overflow

SQL Server 2014 error: Arithmetic overflow error converting expression to data type int

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related