docs.intersystems.com
Home  /  Application Development: Core Topics  /  InterSystems SQL Reference  /  SQL Functions  /  ROUND


InterSystems SQL Reference
ROUND
[Back]  [Next] 
InterSystems: The power behind what matters   
Search:  


A numeric function that rounds or truncates a number at a specified number of digits.
Synopsis
ROUND(numeric-expr,scale[,flag])

{fn ROUND(numeric-expr,scale[,flag])}
Arguments
numeric-expr The number to be rounded. A numeric expression.
scale An expression that evaluates to an integer that specifies the number of places to round to, counting from the decimal point. Can be zero, a positive integer, or a negative integer. If scale is a fractional number, InterSystems IRIS rounds it to the nearest integer.
flag Optional — A boolean flag that specifies whether to round or truncate the numeric-expr: 0=round, 1=truncate. The default is 0.
ROUND returns the same data type as numeric-expr.
Description
This function can be used to either round or truncate a number to the specified number of decimal digits.
ROUND rounds or truncates numeric-expr to scale places, counting from the decimal point. When rounding, the number 5 is always rounded up. Leading and trailing zeroes are removed before the ROUND operation.
Note that the ROUND return value is always normalized, removing trailing zeros. For example, ROUND(10.004,2) returns 10, not 10.00. When a fixed number of decimal digits is important — for example, when representing monetary amounts — the user must use the SQL scale parameter to reinstate the proper number of trailing zeros following the rounding operation.
ROUND and TRUNCATE perform similar operations; they both can be used to decrease the number of significant decimal or integer digits of a number. ROUND can be used to either round or truncate a number. TRUNCATE can only be used to truncate a number. ROUND returns the same data type as numeric-expr; TRUNCATE returns numeric-expr as data type NUMERIC, unless numeric-expr is data type DOUBLE, in which case it returns data type DOUBLE.
Examples
The following example uses a scale of 0 (zero) to round several fractions to integers. It shows that 5 is always rounded up:
SELECT ROUND(5.99,0) AS RoundUp,
       ROUND(5.5,0) AS Round5,
       {fn ROUND(5.329,0)} AS Roundoff
The following example truncates the same fractional numbers as the previous example:
SELECT ROUND(5.99,0,1) AS Trunc1,
       ROUND(5.5,0,1) AS Trunc2,
       {fn ROUND(5.329,0,1)} AS Trunc3
The following ROUND functions round and truncate a negative fractional number:
SELECT ROUND(-0.987,2,0) AS Round1,
       ROUND(-0.987,2,1) AS Trunc1
The following example rounds off pi to four decimal digits:
SELECT {fn PI()} AS ExactPi, ROUND({fn PI()},4) AS ApproxPi
The following example specifies a scale larger than the number of decimal digits:
SELECT {fn ROUND(654.98700,9)} AS Rounded
it returns 654.987 (InterSystems IRIS removed the trailing zeroes before the rounding operation; no rounding or zero padding occurred).
The following example rounds off the value of Salary to the nearest thousand dollars:
SELECT Salary,ROUND(Salary, -3) AS PayBracket
FROM Sample.Employee
ORDER BY Salary
Note that if Salary is less than five hundred dollars, it is rounded to 0 (zero).
In the following example each ROUND specifies a negative scale as large or larger than the number to be rounded:
SELECT {fn ROUND(987,-3)} AS Round1,
       {fn ROUND(487,-3)} AS Round2,
       {fn ROUND(987,-4)} AS Round3,
       {fn ROUND(987,-5)} AS Round4
The first ROUND function returns 1000, because the rounded result has more digits than the scale. The other three ROUND functions return 0 (zero).
See Also