# Using the Oracle CEIL function to round to nearest five thousandth

## The Oracle CEIL function can be used to round to the nearest five thousandth. Learn how to do this in this tip from SQL expert Karen Morton.

Rudy Limeback answered a question about using the SQL ceiling function to round to the nearest thousandth. Do you...

have a solution for rounding to the nearest five thousandth? For example, if the starting number is 21,489.35 the result would be 25,000. If the number is 25,000.01, the result would be 30,000. Thanks.

I believe Rudy's answer was for SQL Server, but the Oracle CEIL function would work similarly. As Rudy demonstrated in his answer to round to the nearest thousandth, here's the same example using the Oracle CEIL function:

```SQL>select mynumber
2       , mynumber/1000.0               as dividedby1000
3       , ceil(mynumber/1000.0)      as ceilingresult
4       , ceil(mynumber/1000.0)*1000 as myfinalnumber
5    from mynumbers;

MYNUMBER   DIVIDEDBY1000   CEILINGRESULT   MYFINALNUMBER
--------------- --------------- --------------- ---------------
349            .349               1            1000
999            .999               1            1000
1000               1               1            1000
1001           1.001               2            2000
4590            4.59               5            5000
6659           6.659               7            7000
10432          10.432              11           11000

7 rows selected.
```
```To change it to round to the nearest five thousandth, you simply change the divisor from 1000 to 5000 as follows:

SQL>select mynumber
2       , mynumber/5000.0               as dividedby1000
3       , ceil(mynumber/5000.0)      as ceilingresult
4       , ceil(mynumber/5000.0)*5000 as myfinalnumber
5    from mynumbers;

MYNUMBER   DIVIDEDBY1000   CEILINGRESULT   MYFINALNUMBER
--------------- --------------- --------------- ---------------
349           .0698               1            5000
999           .1998               1            5000
1000              .2               1            5000
1001           .2002               1            5000
4590            .918               1            5000
6659          1.3318               2           10000
10432          2.0864               3           15000

7 rows selected.
```

By the way, if you want to try this specific example yourself, here's the create table and a few inserts to build a test table.

```create table mynumbers (mynumber number) ;
insert into mynumbers values (349);
insert into mynumbers values (999);
insert into mynumbers values (1000);
insert into mynumbers values (1001);
insert into mynumbers values (4590);
insert into mynumbers values (6659);
insert into mynumbers values (10432);
commit ;

```

Close