CODEWITHSUNDEEP
sqloracle-databaserounding
user6255646
user6255646

Reputation:

Oracle rounding digits after comma

I would like to round numbers in Oracle to keep only 2 digits after comma following the value of the third decimal only. Example of what I would like :

1,374 --> 1,37
1,375 --> 1,37 (the 5 need to be rounding down)
1,3756 --> 1,37 (We only look the third decimal, even if the 4th is a 6)
1,376 --> 1,38

How would you do that ? I checked the ROUND() funtion but the behavior is not what I need.

Thank you for your help

Upvotes: 1

Views: 555

Answers (2)

Alex Poole
Alex Poole

Reputation: 191570

You can adjust the value you're rounding slightly, e.g:

round(trunc(<your number>, 3) - 0.001, 2)

The trunc(<your number>, 3) means everything after the third decimal will be ignored, so 1,3756 will be treated the same as 1,375. The - 0.001 then adjusts that truncated value slightly so that the normal round() behaviour will make the tipping point between up and down appear to be at .x6 rather than .x5.

Quick demo:

alter session set nls_numeric_characters =',.';

with t (n) as (
  select 1.37 from dual
  union all select 1.374 from dual
  union all select 1.374999 from dual
  union all select 1.375 from dual
  union all select 1.375001 from dual
  union all select 1.3756 from dual
  union all select 1.375999 from dual
  union all select 1.376 from dual
  union all select 1.37999 from dual
)
select n, round(n, 2) as simple, trunc(n, 3) as tmp1, trunc(n, 3) - 0.001 as tmp2,
  round(trunc(n, 3) - 0.001, 2) as adjusted
from t;

         N     SIMPLE       TMP1       TMP2   ADJUSTED
---------- ---------- ---------- ---------- ----------
      1,37       1,37       1,37      1,369       1,37
     1,374       1,37      1,374      1,373       1,37
  1,374999       1,37      1,374      1,373       1,37
     1,375       1,38      1,375      1,374       1,37
  1,375001       1,38      1,375      1,374       1,37
    1,3756       1,38      1,375      1,374       1,37
  1,375999       1,38      1,375      1,374       1,37
     1,376       1,38      1,376      1,375       1,38
   1,37999       1,38      1,379      1,378       1,38

Upvotes: 1

Ponder Stibbons
Ponder Stibbons

Reputation: 14858

Use round combined with floor or trunc like here:

with t as (
  select column_value val 
    from table (sys.odcinumberlist(1.374, 1.375, 1.3756, 1.376)))
select val, 
       round(trunc(val, 3)-.0001, 2) v1, 
       round((floor(val*1000)-1)/1000, 2) v2
  from t

Output:

       VAL         V1         V2
---------- ---------- ----------
     1,374       1,37       1,37
     1,375       1,37       1,37
    1,3756       1,37       1,37
     1,376       1,38       1,38

Upvotes: 1

Related Questions