How to store decimal values in SQL Server?

Question

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?

marc_s · Answer 1 · 2013-10-24T08:46:32.760

535

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

edited Oct 24 '13 at 08:46

answered May 01 '09 at 21:09

marc_s

732,580
175
1,330
1,459

@marc_s, Is there anyway to store a value "4.5" where the column datatype is decimal(4,2)? When I insert the value, it is getting modified to "4.50" – Arun Feb 10 '15 at 07:23
1

@ArunkumarTK: `decimal(4,2)` allows 2 digits before and 2 digits after the decimal point. "4.5" can be stored no problem - and numerically, "4.5" and "4.50" are **identical** – marc_s Feb 10 '15 at 07:36
But in my case "4.5" and "4.50" are all different. Should I use varchar instead? – Arun Feb 10 '15 at 07:38
3

@ArunkumarTK: ***WHY*** should those be different?? Both are "four and a half" - the value **IS THE SAME**.And ***NO*** don't use a `varchar` to store a decimal value !! – marc_s Feb 10 '15 at 07:39
@marc_s in my case I am going to store product version where 4.5 and 4.50 are different – Arun Feb 10 '15 at 08:45
1

I know this is an old discussion but wouldn't that be '4.05' and '4.5' in that case @ArunkumarTK. – Shelby115 Sep 02 '15 at 20:21
@ArunkumarTK it definitely sounds like you should be storing varchar if you need exact control over characters etc. But beware if you try to do any math with varchars. – Don Cheadle Oct 07 '15 at 17:35
`DECIMAL(19, 4)` **is a popular choice** check [this](http://stackoverflow.com/questions/224462/storing-money-in-a-decimal-column-what-precision-and-scale) also check [here](http://www.thefinancials.com/Default.aspx?SubSectionID=curformat) World Currency Formats to decide how many decimal places to use , hope helps. – Shaiju T Oct 28 '15 at 12:48
4

Yeah, why is 18 so popular (e.g. the default (18,0)) when both 18 and 19 [use 9 bytes](https://learn.microsoft.com/en-us/sql/t-sql/data-types/decimal-and-numeric-transact-sql)? – xr280xr Sep 13 '17 at 19:26
The last sentence from ya answer is the best and actually the only answer that i was lookin fo !! cheers – Ceylan Mumun Kocabaş Jun 25 '19 at 08:57
For a little perspective `DECIMAL(18,0)` is in the hundred quadrillion range. – Jay Aug 21 '19 at 18:34
@xr280xr 18 is used simply because that's the default. If I simply define the column as `DECIMAL`, it's changed to `DECIMAL(18,0) automatically – SeanC Sep 18 '20 at 17:11

score 144 · Answer 2 · edited Aug 15 '23 at 02:57

144

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.

edited Aug 15 '23 at 02:57

Zach W

194
11

answered May 01 '09 at 21:17

DForck42

19,789
13
59
84

4

It is usually denoted as DECIMAL (p,s) where p stands for precision (maximum allowed digits in a number) and s stands for scale (maximum allowed digits after the decimal point). – RBT Oct 21 '16 at 00:41

score 47 · Answer 3 · edited Jul 15 '13 at 19:34

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.

score 39 · Answer 4 · answered Jun 16 '15 at 03:28

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.

Biddut · Answer 5 · 2018-03-23T08:06:18.137

12

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.

edited Mar 23 '18 at 08:06

answered Mar 23 '18 at 02:48

Biddut

418
1
6
17

score 7 · Answer 6 · edited Jul 15 '13 at 19:32

7

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.

edited Jul 15 '13 at 19:32

Rubens Mariuzzo

28,358
27
121
148

answered Feb 17 '13 at 13:34

Raja Done

79
1
1

3

Why this is relevant while the question specified clearly Microsoft SQL Server? – JCKödel Jan 19 '18 at 14:20

score 3 · Answer 7 · edited Jul 15 '13 at 19:34

3

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.

edited Jul 15 '13 at 19:34

Rubens Mariuzzo

28,358
27
121
148

answered May 01 '09 at 21:27

Matthew Flaschen

278,309
50
514
539

score 1 · Answer 8 · answered Nov 22 '17 at 10:28

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)

answered Nov 22 '17 at 10:28

freya

21
1

How to store decimal values in SQL Server?

8 Answers8

Linked