swathi swathi - 7 months ago 39
SQL Question

Arithmetic overflow error on decimal field

I have a field cost with values


I am trying to figure out what would be the datatype for this.

When I give
decimal 15,15
and trying to load data it is throwing me an error

Arithmetic overflow error converting varchar to data type numeric.


The problem is that you are not allocating any length to the value before the decimal.

DECIMAL (15, 15) means that it has a precision of 15 digits after the decimal, but only enough room for 15 digits total - thus leaving no room for values greater than 1.

This means that DECIMAL (15, 15) only supports values in the following range: -0.999999999999999 to 0.999999999999999 (15 digits after the decimal).

You have 18 digits in your first example, so I would recommend using something like DECIMAL (21, 18)

DECIMAL (21, 18) will support values in the range from: -999.999999999999999999 to 999.999999999999999999 (18 digits after the decimal).

But, you should analyze your own data to see what the maximum value would be that you need to support.