|banker’s round for mysql|
|Sunday, 04 December 2011 02:06|
for some reason, nobody has ever exposed the different rounding methods via mysql’s built-in ROUND() function, so if you want something different, you need to add it via a stored function. the function below is based on the T-SQL version here.
CREATE FUNCTION ROUND_TO_EVEN(val DECIMAL(32,16), places INT) RETURNS DECIMAL(32,16) BEGIN RETURN IF(ABS(val - TRUNCATE(val, places)) * POWER(10, places + 1) = 5 AND NOT CONVERT(TRUNCATE(ABS(val) * POWER(10, places), 0), UNSIGNED) % 2 = 1, TRUNCATE(val, places), ROUND(val, places)); END;
use at your own risk. there may be edge conditions where this fails. but this matches up with the python and postgres based system i was crunching data from, except in cases where that system gets it wrong for some reason.
one thing you might notice is that it does not use any string-handling functions like the other “correct” solution floating around out there.
PlanetMySQL Voting: Vote UP / Vote DOWN
read original article