Arithmetic overflow error converting numeric datatype to numeric

Question

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

I'm wondering why the third column is returning with 10 digits after decimal point? then what it should be ? — vhadalgi
– vhadalgi, Commented Dec 13, 2013 at 7:13
A product of two factors will logically result in higher precision. If you round it, you may lose information. — Fabian Bigler
– Fabian Bigler, Commented Dec 13, 2013 at 8:10

Priyank · Accepted Answer · 2013-12-13 07:01:26Z

1

Try This.

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

edited Dec 13, 2013 at 7:01

answered Dec 13, 2013 at 6:17

Priyank

1,38410 silver badges13 bronze badges

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

2 Comments

Snehal Over a year ago

There is no error while insert check the table definition of both the table TNAME2 & TName.

user3085995 Over a year ago

@Snehal: please check with my edit..the third column is not available in TNAME it's a calculated column whose value i want to insert in TNAME2 table's column TOTAL NUMERIC(15,5)

Suraj Singh · Accepted Answer · 2013-12-13 07:40:24Z

1

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

answered Dec 13, 2013 at 7:40

Suraj Singh

4,0871 gold badge23 silver badges37 bronze badges

Comments

Panagiotis Kanavos · Accepted Answer · 2013-12-13 08:21:34Z

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.

Sachin · Accepted Answer · 2013-12-13 07:12:44Z

0

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

answered Dec 13, 2013 at 7:12

Sachin

691 gold badge2 silver badges12 bronze badges

Comments

Hitesh · Accepted Answer · 2013-12-13 07:14:16Z

0

Try this

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

Write the same query in insert, it must work

answered Dec 13, 2013 at 7:14

Hitesh

3,5068 gold badges43 silver badges61 bronze badges

Collectives™ on Stack Overflow

Arithmetic overflow error converting numeric datatype to numeric

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related