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;

1 comment

  1. 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 Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: