<p>We are rewriting our legacy <a href="https://en.wikipedia.org/wiki/Accounting_information_system" rel="nofollow noreferrer">accounting system</a> in VB.NET and SQL Server. We brought in a new team of .NET/ SQL Programmers to do the rewrite. Most of the system is already completed with the dollar amounts using floats. The legacy system language, I programmed in, did not have a float, so I probably would have used a decimal.</p>
<p>What is your recommendation?</p>
<p>Should the float or decimal data type be used for dollar amounts?</p>
<p>What are some of the pros and cons for either?</p>
<p>One <em>con</em> mentioned in our <a href="https://en.wikipedia.org/wiki/Scrum_%28software_development%29#Daily_scrum" rel="nofollow noreferrer">daily scrum</a> was you have to be careful when you calculate an amount that returns a result that is over two decimal positions. It sounds like you will have to round the amount to two decimal positions.</p>
<p>Another <em>con</em> is all displays and printed amounts have to have a <em>format statement</em> that shows two decimal positions. I noticed a few times where this was not done and the amounts did not look correct.  (i.e. 10.2 or 10.2546)</p>
<p>A <em>pro</em> is the float-only approach takes up eight bytes on disk where the decimal would take up nine bytes (decimal 12,2).</p>

[{'answer_id': 61875, 'author': 'Niall', 'author_id': 6049, 'author_profile': 'https://Stackoverflow.com/users/6049', 'pm_score': 2, 'selected': False, 'text': "<p>Floats are not exact representations, precision issues are possible, for example when adding very large and very small values. That's why decimal types are recommended for currency, even though the precision issue may be sufficiently rare.</p>\n\n<p>To clarify, the decimal 12,2 type will store those 14 digits exactly, whereas the float will not as it uses a binary representation internally. For example, 0.01 cannot be represented exactly by a floating point number - the closest representation is actually 0.0099999998</p>\n"}, {'answer_id': 61876, 'author': 'Espo', 'author_id': 2257, 'author_profile': 'https://Stackoverflow.com/users/2257', 'pm_score': 1, 'selected': False, 'text': "<p>Have you considered using the money-data type to store dollar-amounts?</p>\n<p>Regarding the con that decimal takes up one more byte, I would say don't care about it. In 1 million rows you will only use 1 more MB and storage is very cheap these days.</p>\n"}, {'answer_id': 61877, 'author': '1800 INFORMATION', 'author_id': 3146, 'author_profile': 'https://Stackoverflow.com/users/3146', 'pm_score': 1, 'selected': False, 'text': '<p>Whatever you do, you need to be careful of rounding errors. Calculate using a greater degree of precision than you display in.</p>\n'}, {'answer_id': 61878, 'author': 'David Singer', 'author_id': 4618, 'author_profile': 'https://Stackoverflow.com/users/4618', 'pm_score': 2, 'selected': False, 'text': "<p>The only reason to use Float for money is if you don't care about accurate answers.  </p>\n"}, {'answer_id': 61896, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 3, 'selected': False, 'text': '<p>Ask your accountants! They will frown upon you for using float. Like <a href="https://stackoverflow.com/questions/61872/use-float-or-decimal-for-accounting-application-dollar-amount/61878#61878">David Singer said</a>, use float <em>only</em> if you don\'t care for accuracy. Although I would always be against it when it comes to money.</p>\n<p>In accounting software is <em>not</em> acceptable a float. Use decimal with four decimal points.</p>\n'}, {'answer_id': 61994, 'author': 'Rich Schuler', 'author_id': 6391, 'author_profile': 'https://Stackoverflow.com/users/6391', 'pm_score': 5, 'selected': False, 'text': '<p>First you should read <em><a href="http://citeseerx.ist.psu.edu/viewdoc/summary?doi=10.1.1.22.6768" rel="nofollow noreferrer" title="What Every Computer Scientist Should Know About Floating Point Arithmetic">What Every Computer Scientist Should Know About Floating Point Arithmetic</a></em>. Then you should really consider using some type of <a href="http://en.wikipedia.org/wiki/Arbitrary-precision_arithmetic" rel="nofollow noreferrer">fixed point / arbitrary-precision number</a> package (e.g., Java BigNum or Python decimal module). Otherwise, you\'ll be in for a world of hurt. Then figure out if using the native SQL decimal type is enough.</p>\n<p>Floats and doubles exist(ed) to expose the fast <a href="https://en.wikipedia.org/wiki/Intel_8087" rel="nofollow noreferrer">x87 floating-point coprocessor</a> that is now pretty much obsolete. Don\'t use them if you care about the accuracy of the computations and/or don\'t fully compensate for their limitations.</p>\n'}, {'answer_id': 62071, 'author': 'Keith', 'author_id': 905, 'author_profile': 'https://Stackoverflow.com/users/905', 'pm_score': 3, 'selected': False, 'text': '<p>Floating points have unexpected irrational numbers.</p>\n<p>For instance you can\'t store 1/3 as a decimal, it would be 0.3333333333... (and so on)</p>\n<p>Floats are actually stored as a binary value and a power of 2 exponent.</p>\n<p>So 1.5 is stored as 3 x 2 to the -1 (or 3/2)</p>\n<p>Using these base-2 exponents create some odd irrational numbers, for instance:</p>\n<p>Convert 1.1 to a float and then convert it back again, your result will be something like: 1.0999999999989</p>\n<p>This is because the binary representation of 1.1 is actually 154811237190861 x 2^-47, more than a double can handle.</p>\n<p>More about this issue on <a href="http://bizvprog.blogspot.com/2008/05/floating-point-numbers-more-inaccurate.html" rel="nofollow noreferrer">my blog</a>, but basically, for storage, you\'re better off with decimals.</p>\n<p>On Microsoft SQL server you have the <code>money</code> data type - this is usually best for financial storage. It is accurate to 4 decimal positions.</p>\n<p>For calculations you have more of a problem - the inaccuracy is a tiny fraction, but put it into a power function and it quickly becomes significant.</p>\n<p>However decimals aren\'t very good for any sort of maths - there\'s no native support for decimal powers, for instance.</p>\n'}, {'answer_id': 62132, 'author': 'Guy', 'author_id': 993, 'author_profile': 'https://Stackoverflow.com/users/993', 'pm_score': 2, 'selected': False, 'text': '<p>For a banking system I helped develop, I was responsible for the "interest accrual" part of the system.  Each day, my code calculated how much interest had been accrued (earnt) on the balance that day.</p>\n\n<p>For that calculation, extreme accuracy and fidelity was required (we used Oracle\'s FLOAT) so we could record the "billionth\'s of a penny" being accrued.</p>\n\n<p>When it came to "capitalising" the interest (ie. paying the interest back into your account) the amount was rounded to the penny.  The data type for the account balances was two decimal places.  (In fact it was more complicated as it was a multi-currency system that could work in many decimal places - but we always rounded to the "penny" of that currency).  Yes - there where "fractions" of loss and gain, but when the computers figures were actualised (money paid out or paid in) it was always REAL money values.</p>\n\n<p>This satisfied the accountants, auditors and testers.</p>\n\n<p>So, check with your customers.  They will tell you their banking/accounting rules and practices.</p>\n'}, {'answer_id': 62493, 'author': 'David T. Macknet', 'author_id': 6850, 'author_profile': 'https://Stackoverflow.com/users/6850', 'pm_score': 0, 'selected': False, 'text': "<p>Your accountants will want to control how you round.  Using float means that you'll be constantly rounding, usually with a <code>FORMAT()</code> type statement, which isn't the way you want to do it (use <code>floor</code> / <code>ceiling</code> instead).</p>\n\n<p>You have currency datatypes (<code>money</code>, <code>smallmoney</code>), which should be used instead of float or real.  Storing decimal (12,2) will eliminate your roundings, but will also eliminate them during intermediate steps - which really isn't what you'll want at all in a financial application.</p>\n"}, {'answer_id': 62518, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 0, 'selected': False, 'text': '<p>Always use Decimal. Float will give you inaccurate values due to rounding issues.</p>\n'}, {'answer_id': 62553, 'author': 'Mike Dimmick', 'author_id': 6970, 'author_profile': 'https://Stackoverflow.com/users/6970', 'pm_score': 0, 'selected': False, 'text': "<p>Floating point numbers can <em>only</em> represent numbers that are a sum of negative multiples of the base - for binary floating point, of course, that's two.</p>\n\n<p>There are only four decimal fractions representable precisely in binary floating point: 0, 0.25, 0.5 and 0.75. Everything else is an approximation, in the same way that 0.3333... is an approximation for 1/3 in decimal arithmetic.</p>\n\n<p>Floating point is a good choice for computations where the scale of the result is what is important. It's a bad choice where you're trying to be accurate to some number of decimal places.</p>\n"}, {'answer_id': 62781, 'author': 'user6931', 'author_id': 6931, 'author_profile': 'https://Stackoverflow.com/users/6931', 'pm_score': 1, 'selected': False, 'text': '<p>You will probably want to use some form of fixed point representation for currency values.  You will also want to investigate <a href="https://en.wikipedia.org/wiki/Rounding#Round_half_to_even" rel="nofollow noreferrer">banker\'s rounding</a> (also known as &quot;round half to even&quot;).  It avoids bias that exist in the usual &quot;round half up&quot; method.</p>\n'}, {'answer_id': 62802, 'author': 'Darrel Miller', 'author_id': 6819, 'author_profile': 'https://Stackoverflow.com/users/6819', 'pm_score': 3, 'selected': False, 'text': '<p>Just as an additional warning,  SQL Server and the .NET framework use a different default algorithm for rounding.  Make sure you check out the MidPointRounding parameter in Math.Round().  .NET framework uses <a href="https://en.wikipedia.org/wiki/Rounding#Round_half_to_even" rel="nofollow noreferrer">bankers\' rounding</a> by default and SQL Server uses Symmetric Algorithmic Rounding. Check out the Wikipedia article <a href="http://en.wikipedia.org/wiki/Rounding" rel="nofollow noreferrer">here</a>.</p>\n'}, {'answer_id': 66678, 'author': 'TSK', 'author_id': 9959, 'author_profile': 'https://Stackoverflow.com/users/9959', 'pm_score': 8, 'selected': True, 'text': '<blockquote>\n<p>Should Float or Decimal data type be used for dollar amounts?</p>\n</blockquote>\n<p>The answer is easy. Never floats. <em>NEVER</em>!</p>\n<p>Floats were according to <a href="http://ieeexplore.ieee.org/xpl/mostRecentIssue.jsp?punumber=4610933" rel="nofollow noreferrer">IEEE 754</a> always binary, only the new standard <a href="http://www.intel.com/technology/itj/2007/v11i1/s2-decimal/1-sidebar.htm" rel="nofollow noreferrer">IEEE 754R</a> defined decimal formats. Many of the fractional binary parts can never equal the exact decimal representation.</p>\n<p>Any binary number can be written as <code>m/2^n</code> (<code>m</code>, <code>n</code> positive integers), any decimal number as <code>m/(2^n*5^n)</code>.\nAs binaries lack the prime <code>factor 5</code>, all binary numbers can be exactly represented by decimals, but not vice versa.</p>\n<pre><code>0.3 = 3/(2^1 * 5^1) = 0.3\n\n0.3 = [0.25/0.5] [0.25/0.375] [0.25/3.125] [0.2825/3.125]\n\n          1/4         1/8         1/16          1/32\n</code></pre>\n<p>So you end up with a number either higher or lower than the given decimal number. Always.</p>\n<p>Why does that matter? Rounding.</p>\n<p>Normal rounding means 0..4 down, 5..9 up. So it <em>does</em> matter if the result is\neither <code>0.049999999999</code>.... or <code>0.0500000000</code>... You may know that it means 5 cent, but the  the computer does not know that and rounds <code>0.4999</code>... down (wrong) and <code>0.5000</code>... up (right).</p>\n<p>Given that the result of floating point computations always contain small error terms, the decision is pure luck. It gets hopeless if you want decimal round-to-even handling with binary numbers.</p>\n<p>Unconvinced? You insist that in your account system everything is perfectly ok?\nAssets and liabilities equal? Ok, then take each of the given formatted numbers of each entry, parse them and sum them with an independent decimal system!</p>\n<p>Compare that with the formatted sum. Oops, there is something wrong, isn\'t it?</p>\n<blockquote>\n<p>For that calculation, extreme accuracy and fidelity was required (we used Oracle\'s\nFLOAT) so we could record the &quot;billionth\'s of a penny&quot; being accured.</p>\n</blockquote>\n<p>It doesn\'t help against this error. Because all people automatically assume that the computer sums right, and practically no one checks independently.</p>\n'}, {'answer_id': 70868, 'author': 'Peter Stuifzand', 'author_id': 1633, 'author_profile': 'https://Stackoverflow.com/users/1633', 'pm_score': 2, 'selected': False, 'text': '<p>Even better than using decimals is using just plain old integers (or maybe some kind of bigint). This way you always have the highest accuracy possible, but the precision can be specified. For example the number <code>100</code> could mean <code>1.00</code>, which is formatted like this:</p>\n\n<pre><code>int cents = num % 100;\nint dollars = (num - cents) / 100;\nprintf("%d.%02d", dollars, cents);\n</code></pre>\n\n<p>If you like to have more precision, you can change the 100 to a bigger value, like: 10 ^ n, where n is the number of decimals.</p>\n'}, {'answer_id': 110374, 'author': 'Tomer Pintel', 'author_id': 15556, 'author_profile': 'https://Stackoverflow.com/users/15556', 'pm_score': 2, 'selected': False, 'text': '<p>You can always write something like a Money type for .NET.</p>\n<p>Take a look at this article: <a href="http://www.codeproject.com/KB/recipes/MoneyTypeForCLR.aspx" rel="nofollow noreferrer">A Money type for the CLR</a>. The author did an excellent work in my opinion.</p>\n'}, {'answer_id': 257679, 'author': 'Mitch Wheat', 'author_id': 16076, 'author_profile': 'https://Stackoverflow.com/users/16076', 'pm_score': 3, 'selected': False, 'text': '<p>Use SQL Server\'s <strong>decimal</strong> type.</p>\n<p>Do not use <em>money</em> or <em>float</em>.</p>\n<p><em>money</em> uses four decimal places and is faster than using decimal, <em><strong>but</strong></em> suffers from some obvious and some not so obvious problems with rounding (<a href="http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/e19bf068e208dd5b/f79e614829931537?hl=en&amp;lnk=st&amp;q=author%3Akass+money+accuracy&amp;pli=1" rel="nofollow noreferrer">see this connect issue</a>).</p>\n'}, {'answer_id': 257694, 'author': 'Joshua', 'author_id': 14768, 'author_profile': 'https://Stackoverflow.com/users/14768', 'pm_score': 3, 'selected': False, 'text': "<p>I'd recommend using 64-bit integers that store the whole thing in cents.</p>\n"}, {'answer_id': 528264, 'author': 'George', 'author_id': 64187, 'author_profile': 'https://Stackoverflow.com/users/64187', 'pm_score': 2, 'selected': False, 'text': "<p>I had been using SQL's money type for storing monetary values. Recently, I've had to work with a number of online payment systems and have noticed that some of them use integers for storing monetary values. In my current and new projects I've started using integers and I'm pretty content with this solution.</p>\n"}, {'answer_id': 528908, 'author': 'David Thornley', 'author_id': 14148, 'author_profile': 'https://Stackoverflow.com/users/14148', 'pm_score': 3, 'selected': False, 'text': "<p>A bit of background here....</p>\n\n<p>No number system can handle all real numbers accurately.  All have their limitations, and this includes both the standard IEEE floating point and signed decimal.  The IEEE floating point is more accurate per bit used, but that doesn't matter here.</p>\n\n<p>Financial numbers are based on centuries of paper-and-pen practice, with associated conventions.  They are reasonably accurate, but, more importantly, they're reproducible.  Two accountants working with various numbers and rates should come up with the same number.  Any room for discrepancy is room for fraud.</p>\n\n<p>Therefore, for financial calculations, the right answer is whatever gives the same answer as a CPA who's good at arithmetic.  This is decimal arithmetic, not IEEE floating point.</p>\n"}, {'answer_id': 528962, 'author': 'HLGEM', 'author_id': 9034, 'author_profile': 'https://Stackoverflow.com/users/9034', 'pm_score': 2, 'selected': False, 'text': '<p>Another thing you should be aware of in accounting systems is that no one should have direct access to the tables. This means all access to the accounting system must be through <a href="https://en.wikipedia.org/wiki/Stored_procedure" rel="nofollow noreferrer">stored procedures</a>.</p>\n<p>This is to prevent fraud, not just <a href="https://en.wikipedia.org/wiki/SQL_injection" rel="nofollow noreferrer">SQL injection</a> attacks. An internal user who wants to commit fraud should not have the ability to directly change data in the database tables, ever. This is a critical internal control on your system.</p>\n<p>Do you really want some disgruntled employee to go to the backend of your database and have it start writing them checks? Or hide that they approved an expense to an unauthorized vendor when they don\'t have approval authority? Only two people in your whole organization should be able to directly access data in your financial database, your database administrator (DBA) and his backup. If you have many DBAs, only two of them should have this access.</p>\n<p>I mention this because if your programmers used float in an accounting system, likely they are completely unfamiliar with the idea of internal controls and did not consider them in their programming effort.</p>\n'}, {'answer_id': 3991553, 'author': 'Lars Bohl', 'author_id': 438960, 'author_profile': 'https://Stackoverflow.com/users/438960', 'pm_score': 2, 'selected': False, 'text': '<p>Out of the 100 fractions n/100, where n is a natural number such that 0 &lt;= n and n &lt; 100, only four can be represented as floating point numbers. Take a look at the output of this C program:</p>\n\n<pre><code>#include &lt;stdio.h&gt;\n\nint main()\n{\n    printf("Mapping 100 numbers between 0 and 1 ");\n    printf("to their hexadecimal exponential form (HEF).\\n");\n    printf("Most of them do not equal their HEFs. That means ");\n    printf("that their representations as floats ");\n    printf("differ from their actual values.\\n");\n    double f = 0.01;\n    int i;\n    for (i = 0; i &lt; 100; i++) {\n        printf("%1.2f -&gt; %a\\n",f*i,f*i);\n    }\n    printf("Printing 128 \'float-compatible\' numbers ");\n    printf("together with their HEFs for comparison.\\n");\n    f = 0x1p-7; // ==0.0071825\n    for (i = 0; i &lt; 0x80; i++) {\n        printf("%1.7f -&gt; %a\\n",f*i,f*i);\n    }\n    return 0;\n}\n</code></pre>\n'}, {'answer_id': 4001766, 'author': 'Nakilon', 'author_id': 322020, 'author_profile': 'https://Stackoverflow.com/users/322020', 'pm_score': 6, 'selected': False, 'text': '<p>This photo answers:</p>\n\n<p><img src="https://i.stack.imgur.com/RqaoR.jpg" alt="photo1,C.O."></p>\n\n<p>This is another situation: <a href="http://web.archive.org/web/20120731044947/http://www.wwlp.com/dpp/news/i_team/I-Team:Man-gets-a-$0-foreclosure-notice" rel="noreferrer"><em>man from Northampton got a letter stating his home would be seized if he didn\'t pay up zero dollars and zero cents!</em></a></p>\n\n<p><img src="https://i.stack.imgur.com/ssMtJ.jpg" alt="photo2,C.O."></p>\n'}, {'answer_id': 4002088, 'author': 'BrokeMyLegBiking', 'author_id': 97686, 'author_profile': 'https://Stackoverflow.com/users/97686', 'pm_score': 0, 'selected': False, 'text': '<p>This is an excellent article describing <a href="http://sqlblog.com/blogs/hugo_kornelis/archive/2007/10/17/so-called-exact-numerics-are-not-at-all-exact.aspx" rel="nofollow">when to use float and decimal</a>. Float stores an approximate value and decimal stores an exact value.</p>\n\n<p>In summary, exact values like money should use decimal, and approximate values like scientific measurements should use float. </p>\n\n<p>Here is an interesting example that shows that both float and decimal are capable of losing precision. When adding a number that is not an integer and then subtracting that same number  float results in losing precision while decimal does not:</p>\n\n<pre><code>    DECLARE @Float1 float, @Float2 float, @Float3 float, @Float4 float; \n    SET @Float1 = 54; \n    SET @Float2 = 3.1; \n    SET @Float3 = 0 + @Float1 + @Float2; \n    SELECT @Float3 - @Float1 - @Float2 AS "Should be 0";\n\nShould be 0 \n---------------------- \n1.13797860024079E-15\n</code></pre>\n\n<p>When multiplying a non integer and dividing by that same number, decimals lose precision while floats do not.</p>\n\n<pre><code>DECLARE @Fixed1 decimal(8,4), @Fixed2 decimal(8,4), @Fixed3 decimal(8,4); \nSET @Fixed1 = 54; \nSET @Fixed2 = 0.03; \nSET @Fixed3 = 1 * @Fixed1 / @Fixed2; \nSELECT @Fixed3 / @Fixed1 * @Fixed2 AS "Should be 1";\n\nShould be 1 \n--------------------------------------- \n0.99999999999999900\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/61872', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/4964/']