ROUND, TRUNC, CEIL and FLOOR in Oracle Database

I am continuing my efforts to cover the basics yet important topics of Oracle Database. With this post I am trying to provide bit of explanation and some examples of ROUND, TRUNC, CEIL and FLOOR functions in Oracle Database.

ROUND(n,i): ROUND returns n rounded to i places to the right of the decimal point. Default value of i is 0. The argument i can be negative to round off digits left of the decimal point. Following is the example of ROUND function, with i as positive, zero and negative values.

ngarg> select sal, round(sal,1), round(sal,2), round(sal), round(sal,-1), round(sal,-2) from emp;

       SAL ROUND(SAL,1) ROUND(SAL,2) ROUND(SAL) ROUND(SAL,-1) ROUND(SAL,-2)
---------- ------------ ------------ ---------- ------------- -------------
   5000.15       5000.2      5000.15       5000          5000          5000
    2850.3       2850.3       2850.3       2850          2850          2900
   2450.45       2450.5      2450.45       2450          2450          2500
    2975.5       2975.5       2975.5       2976          2980          3000
   3000.55       3000.6      3000.55       3001          3000          3000
    3000.6       3000.6       3000.6       3001          3000          3000
    800.75        800.8       800.75        801           800           800
    1600.9       1600.9       1600.9       1601          1600          1600
      1250         1250         1250       1250          1250          1300
   1250.05       1250.1      1250.05       1250          1250          1300
   1500.95         1501      1500.95       1501          1500          1500
   1100.01         1100      1100.01       1100          1100          1100
    950.99          951       950.99        951           950          1000
      1300         1300         1300       1300          1300          1300

TRUNC(n,i): The TRUNC function returns n truncated to i decimal places. If i is omitted, then n is truncated to 0 places. i can be negative to truncate (make zero) n digits left of the decimal point. Following is the example of TRUNC function, with i as positive, zero and negitive values.

ngarg> select sal, trunc(sal,1), trunc(sal,2), trunc(sal), trunc(sal,-1), trunc(sal,-2) from emp;

       SAL TRUNC(SAL,1) TRUNC(SAL,2) TRUNC(SAL) TRUNC(SAL,-1) TRUNC(SAL,-2)
---------- ------------ ------------ ---------- ------------- -------------
   5000.15       5000.1      5000.15       5000          5000          5000
    2850.3       2850.3       2850.3       2850          2850          2800
   2450.45       2450.4      2450.45       2450          2450          2400
    2975.5       2975.5       2975.5       2975          2970          2900
   3000.55       3000.5      3000.55       3000          3000          3000
    3000.6       3000.6       3000.6       3000          3000          3000
    800.75        800.7       800.75        800           800           800
    1600.9       1600.9       1600.9       1600          1600          1600
      1250         1250         1250       1250          1250          1200
   1250.05         1250      1250.05       1250          1250          1200
   1500.95       1500.9      1500.95       1500          1500          1500
   1100.01         1100      1100.01       1100          1100          1100
    950.99        950.9       950.99        950           950           900
      1300         1300         1300       1300          1300          1300

CEIL(n): The CEIL function returns the smallest INTEGER that is greater than or equal to n.
FLOOR(n): The FLOOR function returns the largest INTEGER that is equal to or less than n.

ngarg> select sal, ceil(sal), floor(sal), round(sal), trunc(sal) from emp;

       SAL  CEIL(SAL) FLOOR(SAL) ROUND(SAL) TRUNC(SAL)
---------- ---------- ---------- ---------- ----------
   5000.15       5001       5000       5000       5000
    2850.3       2851       2850       2850       2850
   2450.45       2451       2450       2450       2450
    2975.5       2976       2975       2976       2975
   3000.55       3001       3000       3001       3000
    3000.6       3001       3000       3001       3000
    800.75        801        800        801        800
    1600.9       1601       1600       1601       1600
      1250       1250       1250       1250       1250
   1250.05       1251       1250       1250       1250
   1500.95       1501       1500       1501       1500
   1100.01       1101       1100       1100       1100
    950.99        951        950        951        950
      1300       1300       1300       1300       1300

Related Posts:
- SQL Interview Question Answers
- Order By and Null values in Oracle Database
- Playing With Truncate and Date
- Oracle: Some Important Date Queries
- Prefer Oracle Native Date Arithmetic over ANSI INTERVAL