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.
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.
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.
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
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
Hi Nimish Garg , can you please share something on 11g RAC !
ReplyDeletecan you share something on node ? How to connect different node ?
ReplyDeletecan u explain about different nodes .....
ReplyDeleteoracle training in chennai
Wonderful blog! Do you have any tips and hints for aspiring writers? Because I’m going to start my website soon, but I’m a little lost on everything. Many thanks! https://www.masterconcreteresurfacing.com.au/
ReplyDeleteThis is so amazing. If you want to instagram followers to increase engagement and reach the target audience. Buy YouTube Subscribers India
ReplyDeleteInformative post. Thank you sharing. Please visit our website to learn new courses.
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteI really liked your blog .Continue to share this sort of information. For additional information on the Fasteners Manufacturers In India
ReplyDeleteThanks for sharing this knowledgeable article and keep posting Interior Designers in Chennai
ReplyDelete