This Q&A was previously posted on the Stack Exchange Network of websites.
This content is licensed under the Creative Commmons Attribution-Sharealike (CC BY-SA) license. As the author of these answers, this archive serves as a canonical location for permalinks, which will outlive Stack Overflow. See all my answers by looking at the Stack Exchange post category.
Monetary value in integer over decimal datatype
Question
I have came across several articles that mentioned that advise on not to store monetary value as decimal, but use integer instead.
The reasoning that it does not store actual value and will cause some rounding difference/errors.
But i have yet ever participate in any project that use an integer datatype for monetary value in database storage.
My question is:
1) Is there any solid business use case/scenario/example of using decimal datatype (19, 4) on monetary value that will result in rounding issue?
As so far i had yet to experience the above mentioned issue.
2) As we all know that fiat currency have range of decimal place of 0 to 3 depending on countries.
Imagine now that we have a system that accepts fiat currencies transfer based on configuration configured in currencies config table.
Is it common to implement a decimal constraint for each currency during insert or we leave it to application level to deal with that?
TransactionLog -------------- CurrencyID UserIDFrom UserIDTo TransactionDateTime CurrencyAmount (decimal(19, 4)
asked 2019-09-15 by c448548
Answer
The reasoning that it does not store actual value and will cause some rounding difference/errors
This is only an issue if you are storing decimals as floating point decimals. A float should always be considered to be an estimate which is subject to rounding errors and variability.
In most RDBMSs, using a decimal () data type is not a floating point decimal, and is not subject to the same variability.
When performing math & other computation, you will need to be conscious of rounding or truncation to ensure stored values meet accepted practices– but you’ll need to do that regardless of whether storing as an int or a decimal.
For example, take the following example on SQL Server (You didn’t specify an RDBMS, but I’m most familiar with SQL Server). This is a simple example that mimics a calculation like “increase by 6.5%”:
DECLARE @intmoney int;
DECLARE @decmoney decimal(19, 4);
SET @intmoney = 1234500 * 1.065;
SET @decmoney = 123.4500 * 1.065;
SELECT IntegerMoney = @intmoney,
DecimalMoney = @decmoney;
The integer money value will be rounded to 1314742.
The decimal money value will be rounded to 131.4743.
Doing the math “by hand” will give a value of 131.47425.
* The integer value has been truncated.
* The decimal value has been rounded .
This is expected behavior based on the different data types. Depending on your application’s rounding rules, one of these behaviors may be more or less desired. If you are storing important financial information, you may want to explicitly specify rounding using the ROUND, CEILING, and FLOOR functions (or equivalent, depending on platform & language). This can ensure you always have the expected behavior and do not suffer from unexpected rounding/truncating issues.
answered 2019-09-15 by Andy Mallon