在SQL server 2008中遇到使用十进制数据类型的算术溢出错误

I have a column declared as decimal(4,4). Currently it stores 0.0400 as the value. Now I need to update the value stored from 0.0400 to 9.95.I used the following query :

我有一个列声明为小数(4,4)。目前它存储0.0400作为值。现在我需要更新存储在0.0400到9.95之间的值。我使用了以下查询:

Update <tablename>
set <columnname>= 9.95
where <condition>

When I try to execute, I get the following error : Arithmetic overflow error converting numeric to data type numeric. The statement has been terminated.

当我尝试执行时，我得到以下错误:算术溢出错误将数值转换为数据类型数值。声明已被终止。

Kindly help.

请帮助。

3 个解决方案

#1

Defining a column as decimal(4,4) is the equivalent of saying

将一列定义为decimal(4,4)等同于将其定义为decimal

I will have 4 digits in total, of which 4 come after the decimal point

总共有4位，其中4位在小数点后

To represent 9.95, you'd need to store it as 9.9500 to satisfy the '4 decimal places' condition. But this now exceeds the 'max 4 digits' condition and so can't be converted.

要表示9.95，需要将其存储为9.9500，以满足“4位小数”条件。但现在这已经超过了“最多4位”的条件，所以不能转换。

You'd need at least decimal(5, 4) to store 9.95 as a decimal in this way.

您至少需要十进制(5,4)以这样的方式存储9.95。

#2

If you write decimal(4,4), what the database hears is:

如果您写入decimal(4,4)，那么数据库听到的是:

There are four digits total
总共有四位数
All four of them are behind the decimal separator
这四个都在十进制分隔符后面

So a decimal(4,4) can store the range 0.0000 to 0.9999 (and its negative equivalent). But 9.95 is outside that range, so it will return an error.

所以一个小数(4,4)可以存储0.0000到0.9999的范围(和它的负等价)。但是9。95不在这个范围之内，所以它会返回一个错误。

#3

I had similar issue few years ago, here is my understanding.

几年前我也有过类似的问题，这是我的理解。

Decimal(TotalDigitsIncludingDecimal,DecimalPlaces)

小数(TotalDigitsIncludingDecimal DecimalPlaces)

Eg: Value = 50.05 declare Decimal(4,2) Eg: Value = 5000.0005 declare Decimal(8,4)

Value = 50.05声明Decimal(4,2) Eg: Value = 5000.0005声明Decimal(8,4)

#1