Removes all digits to the right of the decimal point for any value. Optionally, you can specify the number of digits to which to round. Input can be an Integer, a Decimal, a column reference, or an expression. 


Numeric literal example:

derive type:single value: TRUNC(PI())

Output: Generates a column with each row's value 3.

Expression example:

derive type:single value: TRUNC(length_in * length_in, 2)

Output: Generates a column containing the square of the values in length_in, truncated to two decimal points.

derive type:single value: TRUNC(numeric_value,integer_value)

ArgumentRequired?Data TypeDescription
numeric_valueYstring, decimal, or integerName of column or Decimal or Integer literal to apply to the function
integer_valueNinteger

Number of digits to which to truncate.

  • Default is 0, which truncates to the nearest integer.
  • Negative integer values can be applied.

numeric_value

Name of the column, numeric literal, or numeric expression.

Required?Data TypeExample Value
YesString (column reference) or Integer or Decimal literal2.5

integer_value

Number of digits to which to round the first argument of the function.

Required?Data TypeExample Value
NoInteger literal3

Example - Basic TRUNC

Source:

RowIdmyVal
r011.2345
r02-1.2345
r03100.000
r0410.1
r0550.029

Transform:

derive type:single value:TRUNC(myVal) as: 'trunc_myVal'

derive type:single value:TRUNC(myVal,2) as: 'trunc_myVal2'

derive type:single value:TRUNC(myVal,2) as: 'trunc_myVal_2'

Results:

RowIdmyValtrunc_myValtrunc_myVal2trunc_myVal_2
r011.234511.230
r02-1.2345-1-1.230
r03100.000100100.00100
r0410.11010.10
r0550.0295050.020

Example - RANDBETWEEN, PI, and TRUNC functions