TRUNC (Numbers)
Truncates numbers to a specific digit. You can also use TRUNC to truncate dates.
Syntax
TRUNC( numeric_value [, precision] )
The following table describes the arguments for this command:
Argument | Required/ Optional | Description |
|---|
numeric_value | Required | Numeric datatype. Passes the values you want to truncate. You can enter any valid transformation expression that evaluates to a Numeric datatype. |
precision | Optional | Can be a positive or negative integer. You can enter any valid transformation expression that evaluates to an integer. The integer specifies the number of digits to truncate. |
If precision is a positive integer, TRUNC returns numeric_value with the number of decimal places specified by precision. If precision is a negative integer, TRUNC changes the specified digits to the left of the decimal point to zeros. If you omit the precision argument, TRUNC truncates the decimal portion of numeric_value and returns an integer.
If you pass a decimal precision value, the Data Integration Service rounds numeric_value to the nearest integer before evaluating the expression.
When you run a session in high precision mode, use the ROUND function before truncating.
For example, suppose the following expression is used to truncate the values in the QTY port:
TRUNC ( QTY / 15 )
When the value for QTY = 15000000, the session returns the value 999999. The expected result is 1000000.
At run time, the Data Integration Service evaluates the constant part of the expression and then the variable part.
In the above expression, QTY is the variable value and (1/15) is the constant value.
When QTY = 15000000, the expression is evaluated as follows:
TRUNC ( 15000000 * (1/15)
TRUNC ( 15000000 * (1/15)
= TRUNC ( 15000000 * 0.0666666666666666)
= TRUNC ( 15000000 * 0.0666666666666666)
= TRUNC ( 999999.99999999)
= 999999
If you use the ROUND function before truncating, the expression is evaluated as follows:
TRUNC (ROUND (QTY/15, .99999999999999999999999999)).
Return Value
Numeric value.
NULL if one of the arguments is NULL.
Examples
The following expressions truncate the values in the Price port:
TRUNC( PRICE, 3 )
PRICE | RETURN VALUE |
|---|
12.9995 | 12.999 |
-18.8652 | -18.865 |
56.9563 | 56.956 |
15.9928 | 15.992 |
NULL | NULL |
TRUNC( PRICE, -1 )
PRICE | RETURN VALUE |
|---|
12.99 | 10.0 |
-187.86 | -180.0 |
56.95 | 50.0 |
1235.99 | 1230.0 |
NULL | NULL |
TRUNC( PRICE )
PRICE | RETURN VALUE |
|---|
12.99 | 12.0 |
-18.99 | -18.0 |
56.95 | 56.0 |
15.99 | 15.0 |
NULL | NULL |