swathi - 8 months ago 43

SQL Question

I have a field cost with values

`0.987878656435798654`

`0.765656787898767`

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

When I give

`decimal 15,15`

Arithmetic overflow error converting varchar to data type numeric.

Answer

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.