1

Is there any internal function in Oracle to support Banker's rounding, I need to use half to odd Banker's rounding in a select query

Vivek Gupta
  • 2,534
  • 3
  • 15
  • 28

1 Answers1

5

To round to the nearest odd integer:

CASE
  WHEN MOD( ABS( value ), 2 ) = 1.5
  THEN TRUNC( value )
  ELSE ROUND( value )
END

To round to the nearest odd hundredth:

CASE
  WHEN MOD( ABS( value ), 0.02 ) = 0.015
  THEN TRUNC( value, 2 )
  ELSE ROUND( value, 2 )
END
MT0
  • 143,790
  • 11
  • 59
  • 117
  • how could a MOD with 2 be other than 1 or 0 ? – Vivek Gupta Jul 08 '16 at 11:03
  • Because you are not just passing positive integers into the function: `MOD( 3.5, 2 )` is `1.5` and `MOD( -4.5, 2 )` is `-0.5`. – MT0 Jul 08 '16 at 11:04
  • thanks, this one worked for first decimal digit half to odd, but was looking for an internal Oracle function for this purpose as first priority... – Vivek Gupta Jul 08 '16 at 11:14
  • 1
    @VivekGupta See my previous edit for rounding to nearest odd hundredth - there is no single Oracle function that will do this but `MOD()`, `ABS()`, `TRUNC()` and `ROUND()` are all simple Oracle functions. – MT0 Jul 08 '16 at 11:17