Mysql rounding for currency format


Most of the time we are used to format float numbers into currency format by PHP (or any front end language) using functions like fprintf(). If we could grab the result formatted from the db, we won’t need to use front end formatting. Mysql function format() is the function we need.
Lets say we have a table with float field price and we want to show the price in formatted string. The sql query would be something like this:

select concat('£', format(price, 2)) as price from TABLE_NAME;

The result set will have the formatted output ready to be displayed on the browser. You can see the mysql function detail here.

,

  1. #1 by Will on November 10, 2009 - 6:38 pm

    … except, of course, that nobody would ever store monetary values as a float, because that would be wrong.

  2. #2 by imran on November 12, 2009 - 2:54 pm

    you are kind of correct. Thanks :-)

  3. #3 by Paul Carpenter on June 7, 2011 - 5:44 pm

    Anyone using floats to store monetary values that are then used in calculations to create totals and taxes, is asking for trouble.

    This is the difference between resolution and accuracy. The classic examples with floats is partials equivalent no powers of 2 – 1/3, 1/5, 1/6, 1/7……..

    I have seen the results in many places like dailywtf and pictures of parking charge machines giving totals of $4.18e96 (yes x 10 to the power 96) to never use any float format for anything monetary in normal usage.

    The only time floats are useable is for working out national debt, where the second by second differences between actual and calculated vary so much any figure is a best estimate.

  4. #4 by John Ortiz on March 4, 2012 - 3:26 am

    Thanks for this, but I detect that in negative numbers, minus sign is after currency sign. Do you how to change this behavior?

Comments are closed.