ROUND (for numbers)

The ROUND function returns the value of the first input expression rounded to the number of decimal places specified by the second input argument (scale). If a third argument is present and nonzero, the input expression is truncated.

Syntax

ROUND (input_exp, scale [, modifier] )

Remarks

•

The input expression is the number to round.

•

The input expression data type can be DECIMAL, INTEGER, FLOAT, STRING, or NULL.

•

The scale data type can be DECIMAL, INTEGER, FLOAT, STRING, or NULL.

•

If either the input argument or the scale is NULL, the output is NULL.

•

If the modifier is present and nonzero, the input expression is truncated. If the modifier is absent or zero, the input expression is rounded. The modifier can be TINYINT, SMALLINT, or INT.

•

If scale is less than zero, it is set to zero; if scale is greater than 255, it is set to 255.

•

See About SQL Functions in TDV for an explanation of the DECIMAL(p,s) notation.

The table below shows the effect of scale on different input argument data types.

Data Type of Input Argument	Output Type
DECIMAL(p,q)	DECIMAL(p-q+scale, scale)
TINYINT, SMALLINT, BIGINT, INTEGER, or NUMERIC	DECIMAL(19+scale, scale)
FLOAT, REAL, STRING	DECIMAL(255, scale)
NULL	NULL

Examples

SELECT ROUND (columnX, 2) FROM tableY

If columnX is DECIMAL(10, 6), a value in columnX of 10.666666 is converted to DECIMAL(6, 2) with a value of 10.67.

SELECT ROUND (100.123456, 4)

Result is 100.1235.

SELECT ROUND (100.15, 4)

Result is 100.1500.

SELECT ROUND (100.15, 1, 1)

Because of the nonzero third argument, the result is truncated to 100.1.