[ACCEPTED]-storing money amounts in mysql-fixed-point

Accepted answer

Score: 102

Do not store money values as float, use 7 the DECIMAL or NUMERIC type:

Documentation for MySQL Numeric Types

EDIT & clarification:

Float 6 values are vulnerable to rounding errors 5 are they have limited precision so unless 4 you do not care that you only get 9.99 instead 3 of 10.00 you should use DECIMAL/NUMERIC 2 as they are fixed point numbers which do 1 not have such problems.

Score: 39

It's not generally a good idea to store 2 money as a float as rounding errors can 1 occurr in calculations.

Consider using DECIMAL(10,2) instead.

Score: 20

Does it really matter if it stores is as 6 3.5, 3.50 or even 3.500?

What is really important 5 is how it is displayed after it is retrieved 4 from the db.

Or am I missing something here?

Also 3 don't use a float, use a decimal. Float 2 has all sorts of rounding issue and isn't 1 very big.

Score: 18

To store values you can use a DECIMAL(10,2) field, then 1 you can use the FORMAT function:

SELECT FORMAT(`price`, 2) FROM `table` WHERE 1 = 1

Score: 5

Why do you want to store "3.50" into your 4 database? 3.5 == 3.50 == 3.5000 as far 3 as the database is concerned.

Your presentation 2 and formatting of figures/dates/etc should 1 be done in the application, not the database.

Score: 5

If you use DECIMAL or NUMERIC types, you 4 can declare them as for example DECIMAL(18, 2) which 3 would force 2 decimals even if they were 2 0. Depending on how big values you expect 1 you can change the value of the first parameter.

Score: 1

Binary can't accurately represent floating 3 points with only a limited number of bits. It's 2 not so muuch loss of data but actually conversion 1 errors.. Here's the manual giving examples

You can see this in action in your browser, see for yourself in this code snippet.

<script>

    var floatSum = 0;

    // add 0.1 to floatSum 10 times
    for (var i=0; i<10; i++) {
        floatSum += 0.1;
    }

    // if the repetative adding was correct, the floatSum should be equal to 1
    var expectedSum = 10*0.1; // 1

    // you can see that floatSum does not equal 1 because of floating point error
    document.write(expectedSum + " == " + floatSum + " = " + (expectedSum==floatSum) + "<br />");


    // --- using integers instead ---
    // Assume the example above is adding £0.10 ten times to make £1.00
    // With integers, we will use store money in pence (100 pence (also written 100p) in £1)

    var intSum = 0;

    // add 0.1 to floatSum 10 times
    for (var i=0; i<10; i++) {
        intSum += 10;
    }

    // if the repetative adding was correct, the floatSum should be equal to 1
    var expectedSum = 10*10; // 100

    // you can see that floatSum does not equal 1 because of floating point error
    document.write(expectedSum + " == " + intSum + " = " + (expectedSum==intSum) + "<br />");
    document.write("To display as &pound; instead of pence, we can divide by 100 (presentation only) : &pound;" + intSum/100 + "<br />");
</script>

Source: stackoverflow.com

[ACCEPTED]-storing money amounts in mysql-fixed-point

How to convert packed integer (16.16) fixed-point to float?

Invert 4x4 matrix - Numerical most stable solution needed

If dealing with money in a float is bad, then why does money_format() do it?

Is there a package that maintains a list all currencies with symbols?

How do I add a new Currency to java.util.Currency for an existing country code in Java 7?

Currency input with 2 decimal format

Multi currency - what to store and when to convert?

Use float or decimal for accounting application dollar amount?

Regex for Money

Multi-Currency Best Practice & Implementation