How to store decimal values in SQL Server?
SqlSql ServerTypesDecimalSql Problem Overview
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
I assigned decimal(18, 0) to the column data type but this not allowing me to store these values.
What is the right way to do this?
Sql Solutions
Solution 1 - Sql
DECIMAL(18,0) will allow 0 digits after the decimal point.
Use something like DECIMAL(18,4) instead that should do just fine!
That gives you a total of 18 digits, 4 of which after the decimal point (and 14 before the decimal point).
Solution 2 - Sql
You should use is as follows:
DECIMAL(m,a)
m is the number of total digits your decimal can have.
a is the max number of digits you can have after the decimal point.
http://www.tsqltutorials.com/datatypes.php has descriptions for all the datatypes.
Solution 3 - Sql
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?
If you put PI into a Decimal(18,2) it will be recorded as 3.14?
If you put PI into Decimal(18,10) be recorded as 3.1415926535.
Solution 4 - Sql
For most of the time, I use decimal(9,2) which takes the least storage (5 bytes) in sql decimal type.
Precision => Storage bytes
- 1 - 9 => 5
- 10-19 => 9
- 20-28 => 13
- 29-38 => 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.
Solution 5 - Sql
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.
Solution 6 - Sql
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.
So, if you enter 100.0 in MySQL database, it will show an error like "Out of Range Value for column".
So, you can enter in this range only: from 00.00 to 99.99.
Solution 7 - Sql
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.
Solution 8 - Sql
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)