TRUNC
Returns the expression value fully truncated (toward zero). Supplying a places argument truncates the expression to the number of decimal places you indicate.
Behavior Type
Syntax
TRUNC ( expression [ , places ] )
Parameters
expression |
Is an expression of type |
places |
An INTEGER value. When places is a positive integer, Vertica truncates the value to the right of the decimal point. When places is a negative integer, Vertica truncates the value on the left side of the decimal point. |
Notes
Using TRUNC
with a NUMERIC
datatype returns NUMERIC
, retaining the original precision and scale.
=> SELECT TRUNC(3.5); TRUNC ------- 3.0 (1 row)
Examples
=> SELECT TRUNC(42.8); TRUNC ------- 42.0 (1 row)
=> SELECT TRUNC(42.4382, 2); TRUNC --------- 42.4300 (1 row)
The following example creates a table with two columns, adds one row of values, and shows sample truncating to the left and right of a decimal point.
=> CREATE TABLE sampletrunc (truncol1 NUMERIC, truncol2 NUMERIC); CREATE TABLE => INSERT INTO sampletrunc VALUES (1234567, .1234567); OUTPUT -------- 1 (1 row) => SELECT TRUNC(truncol1,-3) AS p3, TRUNC(truncol1,-4) AS p4, TRUNC(truncol1,-5) AS p5 FROM sampletrunc; p3 | p4 | p5 -------------------------+-------------------------+------------------------- 1234000.000000000000000 | 1230000.000000000000000 | 1200000.000000000000000 (1 row) => SELECT TRUNC(truncol2,3) AS p3, TRUNC(truncol2,4) AS p4, TRUNC(truncol2,5) AS p5 FROM sampletrunc; p3 | p4 | p5 -------------------+-------------------+------------------- 0.123000000000000 | 0.123400000000000 | 0.123450000000000 (1 row)