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

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.

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.

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.

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
```

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.

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.

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 £ instead of pence, we can divide by 100 (presentation only) : £" + intSum/100 + "<br />");
</script>
```

More Related questions

We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.