SQL Server 转换因算术溢出而失败
根据 SQL Server 2008 联机丛书中关于小数和数字数据类型的条目,精度为:
p(精度) 可存储的小数位数的最大总数(包括小数点左侧和右侧)。精度必须是 1 到最大精度 38 之间的值。默认精度为 18。
但是,下面的第二个选择失败,并显示“将 int 转换为数据类型 numeric 时出现算术溢出错误”。
SELECT CAST(123456789 as decimal(9,0))
SELECT CAST(123456789 as decimal(9,1))
According to the entry for decimal and numeric data types in SQL Server 2008 Books Online, precision is:
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.
However, the second select below fails with "Arithmetic overflow error converting int to data type numeric."
SELECT CAST(123456789 as decimal(9,0))
SELECT CAST(123456789 as decimal(9,1))
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
请参阅此处: http://msdn.microsoft.com/ en-us/library/aa258832(SQL.80).aspx
使用:
decimal(p,s)
时,将p
视为您要存储的总位数(无论小数点左边还是右边),并且< code>s 为小数点右侧应有多少个p
数字。您的示例代码失败:
因为:
9=精度(小数点左右的位数)
1=scale(小数点右边的总位数)
(9-1)=8(小数点左边的总位数)
,您的值 123456789 需要小数点左边 9 位。您将需要
decimal(10,1)
或仅decimal(9,0)
see here: http://msdn.microsoft.com/en-us/library/aa258832(SQL.80).aspx
when using:
decimal(p,s)
, think ofp
as how many total digits (regardless of left or right of the decimal point) you want to store, ands
as how many of thosep
digits should be to the right of the decimal point.your sample code fails:
because:
9=precision (total number of digits to left and right of decimal)
1=scale (total number of digits to the right of the decimal)
(9-1)=8 (total digits to the left of the decimal)
and your value 123456789 requires 9 digits to the left of the decimal. you will need
decimal(10,1)
or justdecimal(9,0)
正确的。由于您正在执行
decimal(9,1)
这意味着您总共有 9 位数字,但是,1
保留了其中一位作为小数点右侧,所以你最多可以向左做 8 次,向右做 1 次。Correct. Since you're doing
decimal(9,1)
that means you have 9 total digits, but the,1
is reserving one of them for the right of the decimal place, so you can do at most 8 to the left and 1 to the right.尝试
选择 CAST(123456789 作为十进制(10,1))
try
SELECT CAST(123456789 as decimal(10,1))