### banker’s round for mysql

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.

I think it is generally considered safe with financial data to use a DECIMAL(n,4) for storage as well as for intermediate calculations and use ROUND() to 2 decimals for display of the final result.

If not then why not and what particular problem does your routine solve?