Skip to main content
Previous section   Next section

ROUND

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

Argument Description
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. See $DOUBLE Numbers below.

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. Trailing zeroes are removed after a ROUND round or truncate operation. Leading zeros are not returned.

  • If scale is a positive number, rounding is performed at that number of digits to the right of the decimal point. If scale is equal to or larger than the number of decimal digits, no rounding or zero filling occurs.

  • If scale is zero, rounding is to the closest whole integer. In other words, rounding is performed at zero digits to the right of the decimal point; all decimal digits and the decimal point itself are removed.

  • If scale is a negative number, rounding is performed at that number of digits to the left of the decimal point. If scale is equal to or larger than the number of integer digits in the rounded result, zero is returned.

  • If numeric-expr is zero (however expressed: 00.00, -0, etc.) ROUND returns 0 (zero) with no decimal digits, regardless of the scale value.

  • If numeric-expr or scale is NULL, ROUND returns NULL.

Note that the ROUND return value is always normalized, removing trailing zeros.

ROUND, TRUNCATE, and $JUSTIFY

ROUND and TRUNCATE are numeric functions that perform similar operations; they both can be used to decrease the number of significant decimal or integer digits of a number. ROUND allows you to specify either rounding (the default), or truncation; TRUNCATE does not perform rounding. 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.

ROUND rounds (or truncates) to a specified number of fractional digits, but its return value is always normalized, removing trailing zeros. For example, ROUND(10.004,2) returns 10, not 10.00.

TRUNCATE truncates to a specified number of fractional digits. If the truncation results in trailing zeros, these trailing zeros are preserved. However, if scale is larger than the number of fractional decimal digits in the canonical form of numeric-expr, TRUNCATE does not zero-pad.

Use $JUSTIFY when rounding to a fixed number of fractional digits is important — for example, when representing monetary amounts. $JUSTIFY returns the specified number of trailing zeros following the rounding operation. When the number of digits to round is larger than the number of fractional digits, $JUSTIFY zero-pads. $JUSTIFY also right-aligns the numbers, so that the DecimalSeparator characters align in a column of numbers. $JUSTIFY does not truncate.

$DOUBLE Numbers

$DOUBLE IEEE floating point numbers are encoded using binary notation. Most decimal fractions cannot be exactly represented in this binary notation. When a $DOUBLE value is input to ROUND with a scale value and the rounding flag (flag=0, the default), the return value frequently contains more fractional digits than specified in scale because the fractional decimal result is not representable in binary, so the return value must be rounded to the nearest representable $DOUBLE value, as shown in the following example:

  SET x=1234.5678
  SET y=$DOUBLE(1234.5678)
  &sql(SELECT ROUND(:x,2),ROUND(:y,2) INTO :decnum,:dblnum)
  WRITE "Decimal: ",x," rounded ",decnum,!
  WRITE "Double: ",y," rounded ",dblnum
Copy code to clipboard

If you are using ROUND to truncate a $DOUBLE value (flag=1), the return value for the $DOUBLE is truncated to the number of fractional digits specified by scale. The TRUNCATE function also truncates a $DOUBLE to the number of fractional digits specified by scale.

If you are using ROUND to round a $DOUBLE value and wish to return a specific scale, you should convert the $DOUBLE value to decimal representation before rounding the result.

ROUND with flag=0 (round, the default) returns $DOUBLE("INF") and $DOUBLE("NAN") as the empty string.

ROUND with flag=1 (truncate) returns $DOUBLE("INF") and $DOUBLE("NAN") as INF and NAN.

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
Copy code to clipboard

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
Copy code to clipboard

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
Copy code to clipboard

The following example rounds off pi to four decimal digits:

SELECT {fn PI()} AS ExactPi, ROUND({fn PI()},4) AS ApproxPi
Copy code to clipboard

The following example specifies a scale larger than the number of decimal digits:

SELECT {fn ROUND(654.98700,9)} AS Rounded
Copy code to clipboard

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
Copy code to clipboard

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
Copy code to clipboard

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