SQL Arithmetic overflow error converting numeric to data type numeric
Posted on: March 18, 2016
When you are using straight ADO.Net with SQL you may come when using an operation that produce an overflow. This is often hard to debug if you are inside an update statement which update several fields. You may think than using
convert to the type of the destination field solve the problem, but it is not actually valid.
Here is two examples that show that even if you
cast it won't be enough.
1declare @dob1 as decimal(16,4) set @dob1 = cast(554656545465486786844864613 as decimal(16,4)) select @dob123declare @dob2 as decimal(16,4) set @dob2 = CONVERT(decimal(16,4),5455531234268.68423224224244864613 ) select @dob2
In my case, the problem was that I was performing an update in a field by executing a multiplication. field = field * 1/2. The problem was that field was already, in some case, at 0 and sometime above the maximum which is 12 (16-4) digits. Even if the following code work fine.
1declare @dob3 as int set @dob3 = cast(0*1/2 as int) select @dob3
This one was not:
1declare @fromValue as int declare @toValue as int23set @fromValue = 1 set @toValue = 245SELECT cast(Quantity * @fromValue/@toValue as int) as newQuantity FROM [Trading].[Stock]
However, adding a
where clause eliminates edge cases to be proceeded. The trick is to handle result of 0 and result above the limit.
1declare @fromValue as int declare @toValue as int23set @fromValue = 1 set @toValue = 245SELECT cast(Quantity * @fromValue/@toValue as int) as newQuantity FROM [Trading].[Stock] WHERE Quantity > 0 AND Quantity < 999999999999