+- 0Day Forums (https://0day.red)
+-- Forum: Coding (https://0day.red/Forum-Coding)
+--- Forum: Database (https://0day.red/Forum-Database)
+---- Forum: Microsoft SQL Server (https://0day.red/Forum-Microsoft-SQL-Server)
+---- Thread: How to store decimal values in SQL Server? (/Thread-How-to-store-decimal-values-in-SQL-Server)
How to store decimal values in SQL Server? - zdenkownnfx - 07-31-2023
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?
RE: How to store decimal values in SQL Server? - noella370 - 07-31-2023
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.
RE: How to store decimal values in SQL Server? - bosomsg - 07-31-2023
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`.
RE: How to store decimal values in SQL Server? - quindecillionsaylc - 07-31-2023
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.
RE: How to store decimal values in SQL Server? - baseheartedness658743 - 07-31-2023
`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).
RE: How to store decimal values in SQL Server? - apraxia319693 - 07-31-2023
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.
RE: How to store decimal values in SQL Server? - legateeds - 07-31-2023
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.
[
[To see links please register here]
][1] has descriptions for all the datatypes.[1]:
[To see links please register here]
RE: How to store decimal values in SQL Server? - Sireurybenthic129 - 07-31-2023
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)
RE: How to store decimal values in SQL Server? - Siryvetmimh - 07-31-2023
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.