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 :
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.