Reference Guide > TDV Support for SQL Functions > TDV-Supported Numeric Functions > ROUND (for numbers)
 
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.