GeekInterview.com
Series: Subject: Topic:
Question: 258 of 377

What is the difference between ROUND and TRUNC function in SQL?

Both TRUC and ROUND are single value functions.

TRUNC:

TRUNC function truncates that is in other words cuts off to the digits specified.

The general syntax of TRUNC function is:

TRUNC(number, precision);

For instance:

Suppose the value of salary in employee table for ename= ’Exforsys’ is 55.666
Then

Select TRUNC(salary,2) from employee where ename=’Exforsys’;

Gives output as

TRUNC(salary)
------------------
66.66

ROUND:

ROUND function rounds the number to a specified number of decimal places. The general syntax of TRUNC function is:

ROUND(number, precision);

For instance:

Suppose the value of salary in employee table for ename= ’Exforsys’ is 55.666
Then

Select ROUND(salary,2) from employee where ename=’Exforsys’;

Gives output as

ROUND(salary)
------------------
66.67

Asked by: GeekAdmin | Member Since Oct-2005 | Asked on: Sep 23rd, 2006

View all questions by GeekAdmin   View all answers by GeekAdmin

Showing Answers 1 - 4 of 4 Answers
rakeshsb

Answered On : Sep 12th, 2009

View all answers by rakeshsb

  
Login to rate this answer.
pravin.bhande

Answered On : Oct 17th, 2010

View all answers by pravin.bhande

See below two examples1) select round(1234.1234,2) , round(1234,0) , round(1234,-2) from dual;SQL>round(1234.1234,2) , round(1234.1234,0) , round(1234.1234,-2)___________________________________________1234.13                       1234                            1200In first case, 2nd place after decimal point gets increamented by 1In second case, all values after decimal point omitedIn third case, two places till 2nd place prior to decimal point padded with 0.2) select trunc(1234.1234,2) , trunc(1234,0) , trunc(1234,-2) from dual;SQL>  trunc(1234.1234,2) , trunc(1234,0) , trunc(1234,-2) ____________________________________________1234.12                    1234                                1200In first case, values on right side of decimal point after 2nd place gets omitedIn second case, all values after decimal point omitedIn third case, two places till 2nd place prior to decimal point replacd with 0Note :   ROUND is something related to round figure of value.             TRUNC is something related to truncation of values.

  
Login to rate this answer.
avatar

Answered On : Sep 10th, 2011

Round function will increase the value by 1 if decimal value is greater than or equal to 5 else it will return same integer value without decimal
ex.
select round(8.34) from dual; will return 8;
select round(8.54) from dual; will return 9; as decimal value is .54 which is great than 0.5

Truncate function will truncate the value and wont affect the integer part of value
ex

select truncate(8.34) from dual; will return 8;
select truncate(8.54) from dual; will return 8;


  
Login to rate this answer.
s.siva karthik

Answered On : Sep 29th, 2011

SQL> select ceil(14.1) ,round(14.1),ceil(14.01),round(14.4),trunc(14.6) from dual; CEIL(14.1) ROUND(14.1) CEIL(14.01) ROUND(14.4) TRUNC(14.6) ---------- ----------- ----------- ----------- ----------- 15 14 15 14 14 SQL> select ceil(14.1) ,round(14.1),ceil(14.01),round(14.6),trunc(14.6) from dual; CEIL(14.1) ROUND(14.1) CEIL(14.01) ROUND(14.6) TRUNC(14.6) ---------- ----------- ----------- ----------- ----------- 15 14 15 15 14

Code
  1. SQL> SELECT CEIL(14.1) ,ROUND(14.1),CEIL(14.01),ROUND(14.4),TRUNC(14.6) FROM dual;
  2.  
  3. CEIL(14.1) ROUND(14.1) CEIL(14.01) ROUND(14.4) TRUNC(14.6)                      
  4. ---------- ----------- ----------- ----------- -----------                      
  5.         15          14          15          14          14                      
  6.  
  7. SQL> SELECT CEIL(14.1) ,ROUND(14.1),CEIL(14.01),ROUND(14.6),TRUNC(14.6) FROM dual;
  8.  
  9. CEIL(14.1) ROUND(14.1) CEIL(14.01) ROUND(14.6) TRUNC(14.6)                      
  10. ---------- ----------- ----------- ----------- -----------                      
  11.         15          14          15          15          14                      
  12.  

  
Login to rate this answer.

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

Related Open Questions

Ads

Connect

twitter fb Linkedin GPlus RSS

Ads

Interview Question

 Ask Interview Question?

 

Latest Questions

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.