Re Database Toolbox: "Arithmetic overflow error converting numeric to data type numeric"

Hi! I'm getting the follwoing trying to use update:

[Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error converting numeric to data type numeric.

Google-ing, I find that this error is typically returned when the input exceeds the precision and/or scale of the target, but, at least as far as I can see, this is not the case 'cause my target is type decimal(18,18), but my input has only 15 digits, 1 to the left of and 14 to the right of the decimal place. Any ideas? Thanks!

Answer 1

[David Goldsmith]

Figured it out: I had a vague de ja vu about this when I went and looked at some of the other SQL Server data type options and saw real and float. Sure enough, what was going on was that the value to push was 1.3400, which, evidently, is not exactly expressible in binary, so it was getting converted to 1.3399etc., etc. (out to 14 digits). Furthermore, also evidently, decimal has some safeguard not possessed by real, whereby the former data type balks at the two numbers not being identical, but the latter doesn’t, i.e., the former returns the error, but the latter allows the push...but of the binary approximation, i.e., the result in my database is the 1.3399etc., etc. number. I read that real is synonymous w/ "short" (i.e., four byte mantissa representation), while float allows for "double" (eight byte)--my understanding of all that mumbo jumbo is such that I'll have to find out empirically if that enables the number to be represented as 1.3400(0...0) (or, just as good, 1.339...9 or 1.340...01). Thanks for reading; sorry for the noise.

[David Goldsmith]

Scratch that, I forgot I was in short format display mode: the actual value was in fact 1.339975652228137, I was just seeing it as 1.3400; so I guess 1.339975652228137 is not exactly expressible in whatever decimal(18,18) wanted to convert it to. In any event, I've gone through and changed all the decimal(X,Y)'s to floats--I'll find out the hard way if that breaks things for the other developer (who doesn't know squat about Matlab, and set all this up w/out consulting me...) ;-)

[Kaustubha Govind]

Glad you were able to figure it out. Thanks for posting the solution!

[Korey Hite]

This helped me, thanks!

Answer 2

[Kaustubha Govind]

I don't know anything about SQL, but doesn't decimal(18,18) mean that you can have zero digits before the decimal point, and 18 digits after the decimal point? In other words, you can only store values between 0 and 1e-18. I think your format needs to be at least decimal(18, 17) to store a number that has 1 to the left of and 14 to the right of the decimal place.

[David Goldsmith]

Good guess, but DB people are a different breed than us mathematicians; quoting from

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/9d862a90-e6b7-4692-8605-92358dccccdf.htm:

"decimal[ (p[ , s] )] and numeric[ (p[ , s] )]

Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The ISO synonyms for decimal are dec and dec(p, s). numeric is functionally equivalent to decimal.

p (precision)

The maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.

s (scale)

The maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision."

Note the use of the word maximum; thus decimal(18,18) in DB-speak means not more than 18 digits total, and not more than 18 digits to the right of the decimal point.

Thanks for trying.