This is a bit of a weird one.

Before we go any further, I have tried switching the field to INT, FLOAT, DOUBLE and DECIMAL with the following results:

INT - Doesn't seem to allow me to specify 2 decimal places, keeps setting it to 0

FLOAT - Doesn't seem to allow me to specify 2 decimal places, keeps setting it to 0

DOUBLE - Doesn't seem to allow me to specify 2 decimal places, keeps setting it to 0

DECIMAL - Removes all decimal places completely. Unacceptable for currency field.

So the problem is this.

I have a table which I store customer payments. When storing the payment data, the field value is in #.## format. Recently, just did a bulk update for all customers in the system to give a discount off a flat amount each. When I queried the database for a SUM of this amount instead of :

0.50 +

0.50 +

0.50 +

= 1.50

It gave

0.50 +

0.50 +

0.50 +

= 1.501837782989874

Which makes absolutely ZERO sense whatsoever, until I do a bit more research. Some people have mentioned something about switching the field type to INT at 2 decimals (

http://bytes.com/topic/mysql/answers/862239-sum-increases-precision-decimal-point ) -- which is why I tried this to no avail. Also found a really interesting doc on precision storage issues.

The long and short, what is a quick and dirty way to fix this. I do not need any more than 2 decimal places stored as this is a simple currency value.

Open in new window