Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

TRUNC Function

The TRUNC function truncates a number and right-fills it with zeros from the position specified. This function also truncates a time stamp and returns a date.

Syntax

Copy
TRUNC(number [ , integer ] | timestamp )

Arguments

number

Numeric data type to be truncated. SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, and DOUBLE PRECISION data types are supported.

integer (optional)

An integer that indicates the number of decimal places of precision, in either direction. If no integer is provided, the number is truncated as a whole number; if an integer is specified, the number is truncated to the specified decimal place.

timestamp

The function can also return the date from a time stamp. (To return a time stamp value with 00:00:00 as the time, cast the function result to a time stamp.)

Return Type

TRUNC returns the same numeric data type as the first input argument. For time stamps, TRUNC returns a date.

Examples

Truncate the commission paid for a given sales transaction.

Copy
select commission, trunc(commission) from sales where salesid=784; commission | trunc -----------+------- 111.15 | 111 (1 row)

Truncate the same commission value to the first decimal place.

Copy
select commission, trunc(commission,1) from sales where salesid=784; commission | trunc -----------+------- 111.15 | 111.1 (1 row)

Truncate the commission with a negative value for the second argument; 111.15 is rounded down to 110.

Copy
select commission, trunc(commission,-1) from sales where salesid=784; commission | trunc -----------+------- 111.15 | 110 (1 row)

Return the date portion from the result of the SYSDATE function (which returns a time stamp):

Copy
select sysdate; timestamp ---------------------------- 2011-07-21 10:32:38.248109 (1 row) select trunc(sysdate); trunc ------------ 2011-07-21 (1 row)

Apply the TRUNC function to a TIMESTAMP column. The return type is a date.

Copy
select trunc(starttime) from event order by eventid limit 1; trunc ------------ 2008-01-25 (1 row)