Categories:
Date & Time Functions

TRUNC

Truncates a date, time, or timestamp to the specified part. For example, truncating a timestamp down to the quarter returns the timestamp corresponding to midnight of the first day of the original timestamp’s quarter.

Provides alternative syntax for DATE_TRUNC (reverses the two arguments).

Note

This function is overloaded; it can also be used as a numeric function to round down numeric expressions.

Syntax

TRUNC( <date_or_time_expr>, <date_or_time_part> )

Arguments

  • date_or_time_expr This must be an expression that evaluates to a DATE or a TIMESTAMP.
  • date_or_time_part This must be one of the values listed in Supported Date and Time Parts.

Returns

The function always returns a DATE.

The date is complete (year, month, and day). Truncation does not remove the month and day; instead it sets them to the earliest date in the specified period. For example, TRUNC(TO_DATE('2013-08-05'), 'QUARTER') returns the first date in the third quarter of the year 2013, which is July 1, 2013.

Usage Notes

Examples

The examples below show date trunction. Note that in all cases the result is a complete date, including month and day, but is the earliest date in the period specified by the date_or_time_expr and date_or_time_part.

select trunc(to_date('2013-05-08'), 'MONTH');
+---------------------------------------+
| TRUNC(TO_DATE('2013-05-08'), 'MONTH') |
|---------------------------------------|
| 2013-05-01                            |
+---------------------------------------+
select trunc(to_date('2013-05-08'), 'QUARTER');
+-----------------------------------------+
| TRUNC(TO_DATE('2013-05-08'), 'QUARTER') |
|-----------------------------------------|
| 2013-04-01                              |
+-----------------------------------------+
select trunc(to_date('2013-05-08'), 'YEAR');
+--------------------------------------+
| TRUNC(TO_DATE('2013-05-08'), 'YEAR') |
|--------------------------------------|
| 2013-01-01                           |
+--------------------------------------+