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

3 次查看（过去 30 天）
David Goldsmith
David Goldsmith 2012-2-15
评论： Korey Hite 2022-3-16
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!

请先登录，再回答此问题。

采纳的回答

David Goldsmith
David Goldsmith 2012-2-15
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 个评论
显示 1更早的评论

请先登录，再进行评论。

更多回答（1 个）

Kaustubha Govind
Kaustubha Govind 2012-2-15
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 个评论
David Goldsmith
David Goldsmith 2012-2-15
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.

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Data Type Conversion 的更多信息

标签

产品

Community Treasure Hunt

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

Start Hunting!

Translated by