- Categories:
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¶
For additional usage notes, see Usage Notes for DATE_TRUNC.
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 |
+--------------------------------------+