Currency Format

[booeyOH]

Hello,
It has recently come to my attention that DOUBLE(10,2) may not be the best way to format my fields that hold US currency values. It seems as if DECIMAL(14,2) may be a better solution. My question is if there is any concern in going in and just changing the fields from DOUBLE(10,2) to DECIMAL(14,2), if there is any possibility to lose or change the currency data that is currently in there.

Thanks,
Booey

 

[Kozusnik]

Read the manual on MySQL Decimal data type first.

http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html

This may help.

http://dev.mysql.com/doc/refman/5.1/en/precision-math-decimal-changes.html

Of course...always backup your data first. No matter how trivial you think the change is. We've all been burned at least once.

Mark

 

[booeyOH]

So if I am running 4.0.24 does that mean I shouldn't use decimal because it is "unpacked"? Any recommendations for storing currency values?

 

[r937]

here's an approach

ALTER the table and add a new DECIMAL(14,2) column

then UPDATE the table and set the new column equal to your old DOUBLE column

then do a SELECT and subtract them, with ORDER BY on the difference

this will let you see if the conversion had any major probelms

then ALTER the table again and drop the old column

r937.com | rudy.ca