Categories:

Date & Time Functions

DATE_TRUNC

Truncates a DATE, TIME, or TIMESTAMP to the specified precision.

Note that truncation is not the same as extraction. For example:

  • Truncating a timestamp down to the quarter returns the timestamp corresponding to midnight of the first day of the quarter for the input timestamp.

  • Extracting the quarter date part from a timestamp returns the quarter number of the year in the timestamp.

Alternatives:

TRUNC

See also:

DATE_PART , EXTRACT

Syntax

DATE_TRUNC( <date_or_time_part>, <date_or_time_expr> )

Returns

The returned value is the same type as the input value.

For example, if the input value is a TIMESTAMP, then the returned value is a TIMESTAMP.

Usage Notes

  • date_or_time_part must be one of the values listed in Supported Date and Time Parts.

    When date_or_time_part is week (or any of its variations), the output is controlled by the WEEK_START session parameter. For more details, including examples, see Calendar Weeks and Weekdays.

  • date_or_time_expr can be a date, time, or timestamp.

Examples

Truncate a date (from a timestamp) down to the year, month, and day:

SELECT TO_DATE('2015-05-08T23:39:20.123-07:00') AS "DATE1",
       DATE_TRUNC('YEAR', "DATE1") AS "TRUNCATED TO YEAR",
       DATE_TRUNC('MONTH', "DATE1") AS "TRUNCATED TO MONTH",
       DATE_TRUNC('DAY', "DATE1") AS "TRUNCATED TO DAY";
+------------+-------------------+--------------------+------------------+
| DATE1      | TRUNCATED TO YEAR | TRUNCATED TO MONTH | TRUNCATED TO DAY |
|------------+-------------------+--------------------+------------------|
| 2015-05-08 | 2015-01-01        | 2015-05-01         | 2015-05-08       |
+------------+-------------------+--------------------+------------------+

Truncate a TIMESTAMP down to the hour, minute and second. Note that in all cases, the returned value is a TIMESTAMP, but with zeroes for the portions (e.g. fractional seconds) that were truncated:

SELECT TO_TIMESTAMP('2015-05-08T23:39:20.123-07:00') AS "TIMESTAMP1",
       DATE_TRUNC('HOUR', "TIMESTAMP1") AS "TRUNCATED TO HOUR",
       DATE_TRUNC('MINUTE', "TIMESTAMP1") AS "TRUNCATED TO MINUTE",
       DATE_TRUNC('SECOND', "TIMESTAMP1") AS "TRUNCATED TO SECOND";
+-------------------------+-------------------------+-------------------------+-------------------------+
| TIMESTAMP1              | TRUNCATED TO HOUR       | TRUNCATED TO MINUTE     | TRUNCATED TO SECOND     |
|-------------------------+-------------------------+-------------------------+-------------------------|
| 2015-05-08 23:39:20.123 | 2015-05-08 23:00:00.000 | 2015-05-08 23:39:00.000 | 2015-05-08 23:39:20.000 |
+-------------------------+-------------------------+-------------------------+-------------------------+

Truncate a time down to the minute:

SELECT TO_TIME('23:39:20.123') AS "TIME1",
       DATE_TRUNC('MINUTE', "TIME1") AS "TRUNCATED TO MINUTE";
+----------+---------------------+
| TIME1    | TRUNCATED TO MINUTE |
|----------+---------------------|
| 23:39:20 | 23:39:00            |
+----------+---------------------+

Contrast trunction with extraction:

SELECT DATE_TRUNC('QUARTER', '2019-08-01 12:34:56.789'::TIMESTAMP_NTZ) AS "TRUNCATED",
       EXTRACT(   'QUARTER', '2019-08-01 12:34:56.789'::TIMESTAMP_NTZ) AS "EXTRACTED";
+-------------------------+-----------+
| TRUNCATED               | EXTRACTED |
|-------------------------+-----------|
| 2019-07-01 00:00:00.000 |         3 |
+-------------------------+-----------+