When you assign a float value to a decimal in SQL, it always rounds down.
PHP Code:
E.g. float(8) to decimal(11,2)
E.g. 611.0899999 -> 611.08
E.g. 1070.5800000000002 -> 1070.58
I want to convert some floats to 11,2 decimals, but I want them to half adjust, not round down. These floats were originally 2dp values when stored, so they are all nnn.nn999999 or nn.nn00000n.
The round() function is inconsistent when run on a float. Sometimes it does not round to the closest
E.g. float(8) to decimal(11,2), cast(round(floatVal,2) as decimal(11,2))
E.g. round(1070.5800000000002,2) -> 1070.57 not 1070.58
I have two hackish workarounds to that.
1, do a manual 2dp round, by adding 0.005 and then rounding down
E.g. float(8) to decimal(11,2), cast(floatVal+0.005 as decimal(11,2))
E.g. round(611.0899999+0.005,2) -> 611.09 = correct
E.g. round(1070.5800000000002+0.005,2) -> 1070.58 = correct
2, cast the float value to a 3dp, and then round to a 2dp
E.g. float(8) to decimal(11,2), cast(round(cast(floatVal as decimal(12,3)),2) as decimal(11,2))
E.g. round(cast(611.0899999 as decimal(12,3)),2) -> 611.09 = correct
E.g. round(cast(1070.5800000000002 as decimal(12,3)) -> 1070.58 = correct