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 ] )

Arguments

expression

Resolves to a value of type NUMERIC or DOUBLE PRECISION (FLOAT).

places

INTEGER value:

• Positive: Vertica truncates the value to the right of the decimal point.
• Negative: 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)