ROUND (Numbers)
Rounds numbers to a specified number of digits or decimal places. You can also use ROUND to round dates.
Syntax
ROUND( numeric_value [, precision] )
The following table describes the arguments for this command:
Argument | Required/ Optional | Description |
|---|
numeric_value | Required | Numeric datatype. You can enter any valid transformation expression. Use operators to perform arithmetic before you round the values. |
precision | Optional | Positive or negative integer. If you enter a positive precision, the function rounds to this number of decimal places. For example, ROUND(12.99, 1) returns 13.0 and ROUND(15.44, 1) returns 15.4. If you enter a negative precision, the function rounds this number of digits to the left of the decimal point, returning an integer. For example, ROUND(12.99, -1) returns 10 and ROUND(15.99, -1) returns 20. If you enter decimal precision, the function rounds to the nearest integer before evaluating the expression. For example, ROUND(12.99, 0.8) returns 13.0 because the function rounds 0.8 to 1 and then evaluates the expression. If you omit the precision argument, the function rounds to the nearest integer, truncating the decimal portion of the number. For example, ROUND(12.99) returns 13. |
Return Value
Numeric value.
If one of the arguments is NULL, ROUND returns NULL.
Examples
The following expression returns the values in the Price port rounded to three decimal places:
ROUND( PRICE, 3 )
PRICE | RETURN VALUE |
|---|
12.9936 | 12.994 |
15.9949 | 15.995 |
-18.8678 | -18.868 |
56.9561 | 56.956 |
NULL | NULL |
You can round digits to the left of the decimal point by passing a negative integer in the precision argument:
ROUND( PRICE, -2 )
PRICE | RETURN VALUE |
|---|
13242.99 | 13200.0 |
1435.99 | 1400.0 |
-108.95 | -100.0 |
NULL | NULL |
If you pass a decimal value in the precision argument, the Data Integration Service rounds it to the nearest integer before evaluating the expression:
ROUND( PRICE, 0.8 )
PRICE | RETURN VALUE |
|---|
12.99 | 13.0 |
56.34 | 56.3 |
NULL | NULL |
If you omit the precision argument, the function rounds to the nearest integer:
ROUND( PRICE )
PRICE | RETURN VALUE |
|---|
12.99 | 13.0 |
-15.99 | -16.0 |
-18.99 | -19.0 |
56.95 | 57.0 |
NULL | NULL |
Tip
You can also use ROUND to explicitly set the precision of calculated values and achieve expected results. When the Data Integration Service runs in low precision mode, it truncates the result of calculations if the precision of the value exceeds 15 digits. For example, you might want to process the following expression in low precision mode:
7/3 * 3 = 7
In this case, the Data Integration Service evaluates the left hand side of the expression as 6.999999999999999 because it truncates the result of the first division operation. The Data Integration Service evaluates the entire expression as FALSE. This may not be the result you expect.
To achieve the expected result, use ROUND to round the truncated result of the left hand side of the expression to the expected result. The Data Integration Service evaluates the following expression as TRUE:
ROUND(7/3 * 3) = 7