Table of Contents

Search

  1. Preface
  2. The Transformation Language
  3. Constants
  4. Operators
  5. Variables
  6. Dates
  7. Functions

Transformation Language Reference

Transformation Language Reference

ROUND (Numbers)

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.
If the return value is Decimal with precision greater than 15, you can enable high precision to ensure decimal precision up to 38 digits.

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

0 COMMENTS

We’d like to hear from you!