ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Convert float to decimal, with half adjust?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Convert float to decimal, with half adjust?

    When you assign a float value to a decimal in SQL, it always rounds down.
    PHP Code:
    E.gfloat(8to decimal(11,2)
    E.g611.0899999 -> 611.08
    E
    .g1070.5800000000002 -> 1070.58
    I want to convert some floats to 11
    ,2 decimalsbut I want them to half adjustnot round downThese floats were originally 2dp values when storedso they are all nnn.nn999999 or nn.nn00000n.

    The round() function is inconsistent when run on a floatSometimes it does not round to the closest
    E
    .gfloat(8to decimal(11,2), cast(round(floatVal,2) as decimal(11,2))
    E.ground(1070.5800000000002,2) -> 1070.57 not 1070.58

    I have two hackish workarounds to that
    .

    1, do a manual 2dp roundby adding 0.005 and then rounding down
    E
    .gfloat(8to decimal(11,2), cast(floatVal+0.005 as decimal(11,2))
    E.ground(611.0899999+0.005,2) -> 611.09 correct
    E
    .ground(1070.5800000000002+0.005,2) -> 1070.58 correct

    2
    cast the float value to a 3dp, and then round to a 2dp
    E
    .gfloat(8to decimal(11,2), cast(round(cast(floatVal as decimal(12,3)),2) as decimal(11,2))
    E.ground(cast(611.0899999 as decimal(12,3)),2) -> 611.09 correct
    E
    .ground(cast(1070.5800000000002 as decimal(12,3)) -> 1070.58 correct 
    Last edited by jamief; January 17, 2019, 02:30 PM.
Working...
X