TRUNC

Description

TRUNC

is a row function that truncates a

DATE

value to the specified format.

Syntax

TRUNC

(

date

, "

format

")

Return Value

Returns a value of type

DATE

truncated to the specified format.

Input Parameters

date: Required. A field or expression that returns a
DATE
value.
format: Required. One of the following format values:
millisecond

- Returns a date value truncated to millisecond granularity. Has no effect since millisecond is already the most granular format for date values. For example, an input date value of
2012-08-15 20:38:40.213
would return a date value of
2012-08-15 20:38:40.213
.
second

- Returns a date value truncated to second granularity. For example, an input date value of
2012-08-15 20:38:40.213
would return a date value of
2012-08-15 20:38:40.000
.
minute

- Returns a date value truncated to minute granularity. For example, an input date value of
2012-08-15 20:38:40.213
would return a date value of
2012-08-15 20:38:00.000
.
hour

- Returns a date value truncated to hour granularity. For example, an input date value of
2012-08-15 20:38:40.213
would return a date value of
2012-08-15 20:00:00.000
.
day

- Returns a date value truncated to day granularity. For example, an input date value of
2012-08-15 20:38:40.213
would return a date value of
2012-08-15 00:00:00.000
.
week

- Returns a date value truncated to the first day of the week (starting on a Monday). For example, an input date value of
2012-08-15
(a Wednesday) would return a date value of
2012-08-13
(the Monday prior).
month

- Returns a date value truncated to the first day of the month. For example, an input date value of
2012-08-15
would return a date value of
2012-08-01
.
quarter

- Returns a date value truncated to the first day of the quarter (January 1, April 1, July 1, or October 1). For example, an input date value of
2012-08-15 20:38:40.213
would return a date value of
2012-07-01
.
year

- Returns a date value truncated to the first day of the year (January 1). For example, an input date value of
2012-08-15
would return a date value of
2012-01-01
.
weekyear

- Returns a date value trucated to the first day of the ISO weekyear (the ISO week starting with the Monday which is nearest in time to January 1). For example, an input date value of
2008-08-15
would return a date value of
2007-12-31
. The first day of the ISO weekyear for 2008 is December 31, 2007 (the prior Monday closest to January 1).

Examples

Truncate the order_date
date field to day granularity:

TRUNC([order_date],"day")

Cast the value of the order_date

TEXT

field to a date value using

TO_DATE

, and truncate it to day granularity:

TRUNC(TO_DATE([order_date], "MM/dd/yyyy HH:mm:ss"), "day")