CODEWITHSUNDEEP
sqlsql-server-2008
swathi
swathi

Reputation: 417

Arithmetic overflow error on decimal field

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 and trying to load data it is throwing me an error

Arithmetic overflow error converting varchar to data type numeric.

Upvotes: 3

Views: 6093

Answers (2)

S3S
S3S

Reputation: 25112

Try this...

SELECT LEN(YourColumn)
FROM YourTable

Then , if they are below 1 every time, try this...

SELECT CONVERT(DECIMAL(X,X-1),YourColumn)

Where X is what is returned in the LEN statement. and X-1 is one less than that.

Remember, it's DECIMAL(TotalLength,Precision) so you need to make sure have enough space for the total value.

Upvotes: 1

Siyual
Siyual

Reputation: 16917

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.

Upvotes: 4

Related Questions