Menu
Standard SQL requires that DECIMAL(5,2) be able to store any value with five digits and two decimals, so values that can be stored in the salary column range from -999.99 to 999.99 . In standard SQL, the syntax DECIMAL( M ) is equivalent to DECIMAL( M ,0) .
The Basic syntax of Decimal data type in SQL Server Where, p stands for Precision, the total number of digits in the value, i.e. on both sides of the decimal point. s stands for Scale, number of digits after the decimal point.
In SQL Server, the numeric and decimal data types are equivalent. Both data types let you store integers that have up to 38 digits.
The significant digits to the left of the decimal and the significant digits to the right of the decimal are stored in separate groups of bytes. At the maximum precision specification, DECIMAL(32,s) data types can store s-1 decimal digits to the right of the decimal point, if s is an odd number.
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).
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.
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.
For most of the time, I use decimal(9,2) which takes the least storage (5 bytes) in sql decimal type.
Precision => Storage bytes
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.
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.
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.
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.
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