Menu
Is it appropriate to use the double type to store percentage values (for example a discount percentage in a shop application) or would it be better to use the decimal type?
895
decimal(p,s) / float(n) / real – Are used to store decimal numbers. We can expect that most numerical values we want to store are actually decimal values – percentage, graphical coordinates, sports results etc. bit – Uses only 1 bit to store value 0 or 1 (NULL if not defined).
Percentage was added as fourth numeric data type to the Query Editor/M in November of 2016. Unlike whole number, fixed decimal number, and decimal number, this type does not have a corresponding type in the data model. When loaded to the data model, the percentage data type is represented as a decimal number type.
You should use decimal(p,s) in 99.9% of cases. Percent is only a presentation concept: 10% is still 0.1. Simply choose precision and scale for the highest expected values/desired decimal places when expressed as real numbers. You can have p = s for values < 100% and simply decide based on decimal places.
The most common way to represent a percentage is with floating-point numbers, that way you have some decimal precision such as 33.25 percent. The thing to always remember is what percent really means. It means Per 100.
Floating-point types (float and double are particularly ill-suited to financial applications.
Financial calculations are almost always decimal, while floating-point types are almost always binary. Many common values that are easy to represent in decimal are impossible to represent in binary. For example, 0.2d = 0.00110011...b. See http://en.wikipedia.org/wiki/Binary_numeral_system#Fractions_in_binary for a good discussion.
It's also worth talking about how you're representing prices in your system. decimal is a good choice, but floating point is not, for reasons listed above. Because you believe in Object Oriented Programming, you're going to wrap that decimal in a new Money type, right? A nice treatment of money comes in Kent Beck's Test Driven Development by Example.
Perhaps you will consider representing percentages as an integer, and then dividing by 100 every time you use it. However, you are setting yourself up for bugs (oops, I forgot to divide) and future inflexibility (customer wants 1/10ths of a percent, so go fix every /100 to be /1000. Oops, missed one - bug.)
That leaves you with two good options, depending on your needs. One is decimal. It's great for whole percentages like 10%, but not for things like "1/3rd off today only!", as 1/3 doesn't represent exactly in decimal. You'd like it if buying 3 of something at 1/3rd off comes out as a whole number, right?
Another is to use a Fraction type, which stores an integer numerator and denominator. This allows you to represent exact values for all rational numbers. Either implement your own Fraction type or pick one up from a library (search the internet).
134
You can probably get away with saving the discount percentage as an integer. Just store 10 or 25 or whatever, and when you need to work out the price of something:
newprice = price * discount / 100 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
