SQL Server强制转换失败并发生算术溢出 [英] SQL Server cast fails with arithmetic overflow

问题描述

根据SQL Server 2008联机丛书中十进制和​​数字数据类型的条目，精度为:

According to the entry for decimal and numeric data types in SQL Server 2008 Books Online, precision is:

p(精度) 小数点左边和右边可以存储的最大十进制数字总数.精度必须是1到最大精度38之间的值.默认精度是18.

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.

但是，下面的第二个选择失败，并出现将int转换为数值数据类型的算术溢出错误".

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))

推荐答案

请参见此处: http://msdn.microsoft.com/zh-cn/library/aa258832(SQL.80).aspx

十进制[(p [，s])]

p(精度)指定小数位数的最大总数 可以存储，都在左边 并位于小数点右边. 精度必须是从1开始的值 通过最大的精度.这 最大精度为38.默认 精度为18.

p (precision) Specifies 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. The maximum precision is 38. The default precision is 18.

s(小数位数)指定可以使用的最大十进制数字 存储在小数点右边 观点.小数位数必须为0以上的值 通过p.比例只能指定 如果指定了精度.默认值 小数位数为0；因此，0 ＜＝ s ＜＝ p. 最大存储大小根据 精度.

s (scale) Specifies 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.

在使用:decimal(p,s)时，将p视为要存储的总位数(不考虑小数点的左边或右边)，而s则应存储多少p个位数在小数点右边.

when using: decimal(p,s), think of p as how many total digits (regardless of left or right of the decimal point) you want to store, and s as how many of those p digits should be to the right of the decimal point.

DECIMAL(10,5)=     12345.12345
DECIMAL(10,2)=  12345678.12
DECIMAL(10,10)=         .1234567891
DECIMAL(11,10)=        1.1234567891

您的示例代码失败:

SELECT CAST(123456789 as decimal(9,1))

因为:

9 =精度(小数点左右的位数)
1 =刻度(小数点右边的总位数)
(9-1)= 8(小数点左边的总位数)

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)

，则您的值123456789需要在小数点左边添加9位数字.您将需要decimal(10,1)或仅decimal(9,0)

and your value 123456789 requires 9 digits to the left of the decimal. you will need decimal(10,1) or just decimal(9,0)

这篇关于SQL Server强制转换失败并发生算术溢出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！