如何在SQL Server中存储十进制值?

I'm trying to figure out decimal data type of a column in the SQL Server. I need to be able to store values like 15.5, 26.9, 24.7, 9.8, etc

我正在计算SQL服务器中列的十进制数据类型。我需要能够存储15.5、26.9、24.7、9.8等值

I assigned decimal(18, 0) to the column data type but this not allowing me to store these values.

我为列数据类型分配了decimal(18,0)，但这并不允许我存储这些值。

What is the right way to do this?

正确的做法是什么?

8 个解决方案

#1

429

DECIMAL(18,0) will allow 0 digits after the decimal point.

十进制(18,0)将允许小数点后的0位。

Use something like DECIMAL(18,4) instead that should do just fine!

使用像DECIMAL(18,4)这样的方法来代替它。

That gives you a total of 18 digits, 4 of which after the decimal point (and 14 before the decimal point).

总共有18位数字，其中4位在小数点后(小数点前14位)。

#2

You should use is as follows:

使用方法如下:

DECIMAL(m,a)

m is the number of total digits your decimal can have.

m是小数位数的总数。

a is the max number of digits you can have after the decimal point.

a是小数点后的最大位数。

http://www.tsqltutorials.com/datatypes.php has descriptions for all the datatypes.

http://www.tsqltutorials.com/datatypes.php对所有数据类型都有描述。

#3

The settings for Decimal are its precision and scale or in normal language, how many digits can a number have and how many digits do you want to have to the right of the decimal point.

十进制的设置是它的精度和比例，或者用普通的语言，一个数字可以有多少位，你希望小数点右边有多少位。

So if you put PI into a Decimal(18,0) it will be recorded as 3?

所以如果你把它写成小数(18,0)它会被记为3?

If you put PI into a Decimal(18,2) it will be recorded as 3.14?

如果你把圆周率写成小数(18,2)会被记为3.14吗?

If you put PI into Decimal(18,10) be recorded as 3.1415926535.

如果你把圆周率写成小数(18,10)就会被记为3.1415926535。

#4

For most of the time, I use decimal(9,2) which takes the least storage (5 bytes) in sql decimal type.

在大多数情况下，我使用十进制(9,2)，它占用了sql decimal类型中最少的存储(5个字节)。

Precision => Storage bytes

精度= >存储字节

1 - 9 => 5
1 - 9 => 5
10-19 => 9
10 - 19 = > 9
20-28 => 13
精神分裂症一般= > 13
29-38 => 17
第29 ~ = > 17

It can store from 0 up to 9 999 999.99 (7 digit infront + 2 digit behind decimal point = total 9 digit), which is big enough for most of the values.

它可以存储从0到9999 999.99(前7位+小数点后2位=总共9位)，这对于大多数值来说都足够大。

#5

In MySQL DB decimal(4,2) allows entering only a total of 4 digits. As you see in decimal(4,2), it means you can enter a total of 4 digits out of which two digits are meant for keeping after the decimal point.

在MySQL DB decimal(4,2)中，只允许输入4位数字。正如您在decimal(4,2)中看到的，它意味着您可以输入总共4个数字，其中有两个数字是用来在小数点后保留的。

So, if you enter 100.0 in MySQL database, it will show an error like "Out of Range Value for column".

因此，如果您在MySQL数据库中输入100.0，它将显示一个错误，如“Out of Range Value for column”。

So, you can enter in this range only: from 00.00 to 99.99.

因此，您只能在这个范围内输入:从00.00到99.99。

#6

The other answers are right. Assuming your examples reflect the full range of possibilities what you want is DECIMAL(3, 1). Or, DECIMAL(14, 1) will allow a total of 14 digits. It's your job to think about what's enough.

其他的答案是对的。假设您的示例反映了所有的可能性，那么您想要的是DECIMAL(3,1)，或者DECIMAL(14,1)将允许总共使用14位数。你的工作就是思考什么是足够的。

#7

You can try this

你可以试试这个

decimal(18,1)

The length of numbers should be totally 18. The length of numbers after the decimal point should be 1 only and not more than that.

数字的长度应该是18。小数点后的数字长度应该是1，而且不能超过1。

#8

-1

request.input("name", sql.Decimal, 155.33)              // decimal(18, 0)
request.input("name", sql.Decimal(10), 155.33)          // decimal(10, 0)
request.input("name", sql.Decimal(10, 2), 155.33)       // decimal(10, 2)

#1

429