CODEWITHSUNDEEP
sqlsql-server-2008t-sql
user3085995
user3085995

Reputation: 453

Arithmetic overflow error converting numeric datatype to numeric

I'm having following columns in a table

SIZE NUMERIC(14,5)    
PRICE NUMERIC(14,5)

when I perform this select query,

SELECT SIZE,
PRICE,
SIZE*PRICE AS TOTAL 
FROM TNAME

I'm getting results:

1.00000 90.00000    90.0000000000
1.00000 90.00000    90.0000000000
1.00000 90.00000    90.0000000000
1.00000 100.00000   100.0000000000
1.00000 30.00000    30.0000000000

I'm wondering why the third column is returning with 10 digits after decimal point?

Also I'm getting

Arithmetic overflow error converting numeric datatype to numeric

while inserting result into another table which has the same columns with same datatype

INSERT INTO TNAME2(SIZE, PRICE, TOTAL)
   SELECT 
       SIZE, PRICE, SIZE * PRICE AS TOTAL 
   FROM 
       TNAME

Upvotes: 0

Views: 2045

Answers (5)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131423

Regarding the first question, the number of decimals, there is nothing wrong. It's the basic, everyday multiplication we learn at school: Multiplying two decimal numbers produces a number with as many decimal digits as the sum of decimal digits of the operands. The number of integer digits can be up to the sum of integer digits of the operands.

Multiplying 10.11 by 12.13 produces 122.6343. It would be VERY awkward if SQL Server broke this basic rule and arbitrarily truncated the result.

As a result, when you try to store the product in a column that accepts fewer digits, you get an overflow error. SQL Server won't change the number of digits automatically because there is no way to decide the correct action.

There are a lot of ways you can handle this, depending on the loss of precision you are willing to suffer:

  • Truncate the extra digits, ie throw them away, accepting up to a unit loss. This can become a LOT of money if you store totals.
  • Round to the desired number of digits. Sounds intuitive, but what about half-way values, ie 0.00005 in your case? Should it be 0.0001 or 0.0000? So we have
  • Rounding up, where 0.5 becomes 1, resulting in up to .5 loss per entry
  • Down, when it becomes 0, with the same loss
  • Round to even or odd, where you round to the nearest odd or even number, which on average produces minimal loss. While this sounds weird, it is the standard defined in IEEE 754. It's also called banker's rounding because it's used in bookkeeping to minimize losses due to truncation.

If you want to store fewer digits you need to decide whether you need to truncate the extra digits or how to round the number, then do it yourself in code.

In your case, you can use CAST to a numeric of the desired precision. This will perform rounding half up, where 0.00005 becomes 0.0001, eg:

INSERT INTO TNAME2(SIZE, PRICE, TOTAL)
SELECT 
   SIZE, PRICE, CAST(SIZE * PRICE as numeric(14,5)) AS TOTAL 
FROM 
   TNAME

SQLFiddle here

This will work, assuming the number of digits doesn't exceed 14, otherwise you will have to change the type of your table field.

If you want some other kind of rounding in SQL, you will have to create your own function.

Upvotes: 1

Suraj Singh
Suraj Singh

Reputation: 4069

INSERT  INTO TNAME2
SELECT    SIZE ,
Price ,
CAST(SIZE * PRICE AS NUMERIC(15, 5))
FROM      TNAME

OR

INSERT  INTO TNAME2 (SIZE,Price,TOTAL)
SELECT    SIZE ,
Price ,
CAST(SIZE * PRICE AS NUMERIC(15, 5)) AS TOTAL
FROM      TNAME

Upvotes: 1

Hitesh
Hitesh

Reputation: 3498

Try this

SELECT SIZE,PRICE,CONVERT(numeric(14,5), SIZE*PRICE) AS TOTAL 
FROM TNAME

Write the same query in insert, it must work

Upvotes: 0

Sachin
Sachin

Reputation: 69

Got below useful link. Multiplication of numerics As per this link you can try as query as below

SELECT SIZE,
PRICE,
CAST(SIZE*PRICE AS numeric(28,5))AS TOTAL 
FROM TNAME

Upvotes: 0

Priyank
Priyank

Reputation: 1384

Try This.

SELECT SIZE,
PRICE,
CAST(SIZE*PRICE AS numeric(14,5))AS TOTAL 
FROM TNAME

Upvotes: 1

Related Questions