Ahfook’s Story » Blog Archive » MySQL round down decimal number to nearest 0 or 5

Jun 4

MySQL round down decimal number to nearest 0 or 5

Inspired by this post, I came out a way to round down a decimal number to nearest 0 or 5 in MySQL.

For example: 1.27 -> 1.25, 3.23->3.20

MySQL code:

SELECT TRUNCATE(value * 2, 1) / 2;

One Response

Sim Says:
June 9th, 2008 at 3:34 pm
The result seem to be not fit in the current 5 cents rounding mechanism in market.
try this
select round(if(mod(value,0.05) >= 0.03,value + (0.05 - mod(value,0.05)),value - mod(value,0.05)),2)

ahfook: Hello Sim, my query only cater for round down :). But thanks for the statement, I believe it does help a lot of people

Leave a Comment