I have really simply question about DECIMAL
(and maybe NUMERIC
) type in SQL Server 2008 R2.
MSDN said:
(scale) The maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p.
I understand this following way:
DECIMAL(10, 5)
- I am able to store 12345.12345 or 12345678.91.DECIMAL(5, 5)
- I can have 12345 or 1234.5 or 1.2345, etc...Is it clear?
But I got this error message:
SELECT CAST(2.8514 AS DECIMAL(5,5))
Arithmetic overflow error converting numeric to data type numeric.
I thought 5,5 means I can have up to 5 digits and up to 5 CAN BE right of the decimal point.
As I tried:
SELECT CAST(12.851 AS DECIMAL(6,5)) - overflows too
however
SELECT CAST(1.23456 AS DECIMAL(6,5)) - is OK.
So what's the truth?
DECIMAL(a,b)
says that I can have up to a digits and JUST b of them are right to the decimal point (and there rest a-b to the left to the dec. point)?
I'm really confused about statement in doc which is copied everywhere. Please take a while and explain me this simple thing.
Lot of thanks!
The easiest way to think of it (for me) is that precision is the total number of digits, of which scale is the number of digits to the right of the decimal point. So DECIMAL(p,s)
means p-s
digits to the left of the point, and s
digits to the right of the point.
That explains all the conversion errors you're seeing: the 2.8514
cannot be decimal(5,5)
because p-s = 0
; 12.851
cannot be decimal(6,5)
because p-s = 1
and so on.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With