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


Questions by GeekAdmin   answers by GeekAdmin

Showing Answers 1 - 4 of 4 Answers

rakeshsb

  • Sep 12th, 2009
 

The decimal point, at the hundreds digit, which is 6. Since the hundreds unit is 6, rounding up occurs and the number returned is 2000. Query 4 has dispensed with the decimal precision parameter. This implies that rounding is done to the nearest whole number. Since the tenth unit is 9, the number is rounded up and 1602 is returned.

The Numeric ROUND Function
The ROUND function performs a rounding operation on a numeric value based on the decimal precision specified. The value returned is either rounded up or down based on the numeric value of the significant digit at the specified decimal precision position. If the specified decimal precision is n, the digit significant to the rounding is found (n + 1) places to the RIGHT of the decimal point. If it is negative, the digit significant to the rounding is found n places to the LEFT of the decimal point. If the numeric value of the significant digit is greater than or equal to 5, a “round up” occurs, else a “round down” occurs.


The ROUND function takes two parameters. Its syntax is ROUND (source number, decimal precision). The source number parameter represents any numeric literal, column, or expression. The decimal precision parameter specifies the degree of rounding and is optional. If the decimal precision parameter is absent, the default degree of rounding is zero, which means the source is rounded to the nearest whole number.


Query 1: select round(1601.916718,1) from dual
Query 2: select round(1601.916718,2) from dual
Query 3: select round(1601.916718,-3) from dual
Query 4: select round(1601.916718) from dual


Query 1 has a decimal precision parameter (n) of 1, which implies that the source number is rounded to the nearest tenth. Since the hundredths (n + 1) digit is 1 (less than 5), no rounding occurs and the number returned is 1601.9.


The decimal precision parameter in query 2 is 2, so the source number is rounded to the nearest hundredth. Since the thousandths unit is 6 (greater than 5), rounding up occurs and the number returned is 1601.92.


The decimal precision parameter of the query 3 is –3.Since it is negative, the digit significant for rounding is found 3 places to the left of the decimal point, at the hundreds digit, which is 6. Since the hundreds unit is 6, rounding up occurs and the number returned is 2000.


Query 4 has dispensed with the decimal precision parameter. This implies that rounding is done to the nearest whole number. Since the tenth unit is 9, the number is rounded up and 1602 is returned.

  Was this answer useful?  Yes

See below two examples

1) 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                            1200

In first case, 2nd place after decimal point gets increamented by 1
In second case, all values after decimal point omited
In 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                                1200

In first case, values on right side of decimal point after 2nd place gets omited
In second case, all values after decimal point omited
In third case, two places till 2nd place prior to decimal point replacd with 0

Note :   ROUND is something related to round figure of value.
             TRUNC is something related to truncation of values.

  Was this answer useful?  Yes

avatar

  • 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;

  Was this answer useful?  Yes

s.siva karthik

  • 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.  

  Was this answer useful?  Yes

Give your answer:

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

Answer Question

Click here to Login / Register your free account


 
Send   Reset

 

Related Answered Questions

 

Related Open Questions