CODEWITHSUNDEEP
sqlsql-servertypesdecimal
Alex
Alex

Reputation: 40395

How to store decimal values in 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

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?

Upvotes: 302

Views: 631218

Answers (8)

DForck42
DForck42

Reputation: 20347

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.

Upvotes: 148

Biddut
Biddut

Reputation: 457

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.

Upvotes: 12

freya
freya

Reputation: 21

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)

Upvotes: 1

Hnin Htet Htet Aung
Hnin Htet Htet Aung

Reputation: 521

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.

Upvotes: 40

marc_s
marc_s

Reputation: 754468

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

Upvotes: 544

Matthew Flaschen
Matthew Flaschen

Reputation: 284796

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.

Upvotes: 3

user99638
user99638

Reputation:

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.

Upvotes: 49

Raja Done
Raja Done

Reputation: 79

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.

Upvotes: 7

Related Questions