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

9 vues (au cours des 30 derniers jours)
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!

Réponse acceptée

David Goldsmith
David Goldsmith le 15 Fév 2012
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.
  3 commentaires
Kaustubha Govind
Kaustubha Govind le 16 Fév 2012
Glad you were able to figure it out. Thanks for posting the solution!

Connectez-vous pour commenter.

Plus de réponses (1)

Kaustubha Govind
Kaustubha Govind le 15 Fév 2012
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.
  1 commentaire
David Goldsmith
David Goldsmith le 15 Fév 2012
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.

Connectez-vous pour commenter.

Catégories

En savoir plus sur Data Type Conversion dans Help Center et File Exchange

Produits

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by