ROUND

The ROUND function returns expression–1 rounded to some number of places to the right or left of the decimal point.

ROUND(expression-1Start of change,0,expression-2End of change)
expression–1
An expression that returns a value of any built-in numeric, character-string, or graphic-string data type. A string argument is converted to double-precision floating point before evaluating the function. For more information about converting strings to double-precision floating point, see DOUBLE_PRECISION or DOUBLE.
If expression-1 is a decimal floating-point data type, the DECFLOAT ROUNDING MODE will not be used. The rounding behavior of ROUND corresponds to a value of ROUND_HALF_UP. If a different rounding behavior is wanted, use the QUANTIZE function.
expression–2
An expression that returns a value of a built-in BIGINT, INTEGER, or SMALLINT data type.

If expression–2 is positive, expression–1 is rounded to the expression–2 number of places to the right of the decimal point.

If expression–2 is negative, expression–1 is rounded to 1 + (the absolute value of expression–2) number of places to the left of the decimal point. If the absolute value of expression–2 is greater than the number of digits to the left of the decimal point, the result is 0. (For example, ROUND(748.58,-4) returns 0.)

Start of changeIf expression–2 is not specified, expression–1 is rounded to zero places to the left of the decimal point. End of change

If expression–1 is positive, a digit value of 5 is rounded to the next higher positive number. If expression–1 is negative, a digit value of 5 is rounded to the next lower negative number.

The data type and length attribute of the result are the same as the data type and length attribute of the first argument, except that precision is increased by one if expression–1 is DECIMAL or NUMERIC and the precision is less than the maximum precision (mp). For example, an argument with a data type of DECIMAL(5,2) will result in DECIMAL(6,2). An argument with a data type of DECIMAL(63,2) will result in DECIMAL(63,2).

If either argument can be null, the result can be null. If either argument is null, the result is the null value.

Examples

  • Calculate the number 873.726 rounded to 2, 1, 0, -1, -2, -3, and -4 decimal places respectively.
      SELECT ROUND(873.726,  2),
             ROUND(873.726,  1),
             ROUND(873.726,  0),
             ROUND(873.726, -1),
             ROUND(873.726, -2),
             ROUND(873.726, -3),
             ROUND(873.726, -4)
        FROM SYSIBM.SYSDUMMY1
    Returns the following values, respectively:
    0873.730   0873.700  0874.000  0870.000  0900.000  1000.000  0000.000
  • Calculate both positive and negative numbers.
      SELECT ROUND( 3.5, 0),
             ROUND( 3.1, 0),
             ROUND(-3.1, 0),
             ROUND(-3.5, 0)
        FROM SYSIBM.SYSDUMMY1

    Returns the following examples, respectively:

    04.0   03.0  -03.0  -04.0