Vivek Gupta Vivek Gupta - 5 months ago 48
SQL Question

Banker's Rounding in Oracle

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

MT0 MT0
Answer

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