View Source

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:

trunc(pi())

Output: Returns the value 3.

Expression example:

trunc(length_in * length_in, 2)

Output: Returns the square of the values in length_in, truncated to two decimal points.

trunc(numeric_value,integer_value)

Argument Required? Data Type Description

numeric_value Y string, decimal, or integer Name of column or Decimal or Integer literal to apply to the function

integer_value

N

integer

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.

Missing input values generate missing results.
Literal numeric values should not be quoted. Quoted values are treated as strings.
Multiple columns and wildcards are not supported.

Required?	Data Type	Example Value
Yes	String (column reference) or Integer or Decimal literal	`2.5`

integer_value

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

Positive values values truncate to the right of the decimal point.
Negative values truncate to the left of the decimal point.
Missing input values generate missing results.

Required?	Data Type	Example Value
No	Integer literal	`3`

Example - Basic TRUNC

Source:

RowId	myVal
r01	1.2345
r02	-1.2345
r03	100.000
r04	10.1
r05	50.029

Transformation:

Results:

RowId	myVal	trunc_myVal	trunc_myVal2	trunc_myVal_2
r01	1.2345	1	1.23	0
r02	-1.2345	-1	-1.23	0
r03	100.000	100	100.00	100
r04	10.1	10	10.1	0
r05	50.029	50	50.02	0

numeric_value

integer_value

Example - Basic TRUNC

Example - RANDBETWEEN, PI, and TRUNC functions