Home » Sql » SQL Arithmetic overflow error converting numeric to data type numeric

SQL Arithmetic overflow error converting numeric to data type numeric

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 cast or 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 convert or cast it won’t be enough.

declare @dob1 as decimal(16,4)
set @dob1 = cast(554656545465486786844864613 as decimal(16,4))
select @dob1

declare @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.

declare @dob3 as int
set @dob3 = cast(0*1/2 as int)
select @dob3

This one was not:

declare @fromValue as int
declare @toValue as int

set @fromValue = 1
set @toValue = 2

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

declare @fromValue as int
declare @toValue as int

set @fromValue = 1
set @toValue = 2

SELECT cast(Quantity * @fromValue/@toValue as int) as newQuantity
FROM [Trading].[Stock]
WHERE Quantity > 0
AND Quantity  < 999999999999

If you like my article, think to buy my annual book, professionally edited by a proofreader. directly from me or on Amazon. I also wrote a TypeScript book called Holistic TypeScript

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.