Categories:: Date & time functions

TRUNC¶

Truncates a DATE, TIME, or TIMESTAMP value to the specified precision. 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.

This function provides an alternative syntax for DATE_TRUNC by reversing the two arguments.

Truncation is not the same as extraction. For example:

Truncating a timestamp down to the quarter using this function 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 using the EXTRACT function returns the quarter number of the year in the timestamp.

Note

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

Alternatives:: DATE_TRUNC
See also:: DATE_PART , EXTRACT

Syntax¶

TRUNC( <date_or_time_expr>, <date_or_time_part> )

Copy

Arguments¶

date_or_time_expr: This argument must evaluate to a date, time, or timestamp.
date_or_time_part: This argument must be one of the values listed in Supported date and time parts.

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¶

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.

Examples¶

The DATE_TRUNC function examples use the data in the following table:

CREATE OR REPLACE TABLE test_date_trunc (
 mydate DATE,
 mytime TIME,
 mytimestamp TIMESTAMP);

INSERT INTO test_date_trunc VALUES (
  '2024-05-09',
  '08:50:48',
  '2024-05-09 08:50:57.891 -0700');

SELECT * FROM test_date_trunc;

Copy

+------------+----------+-------------------------+
| MYDATE     | MYTIME   | MYTIMESTAMP             |
|------------+----------+-------------------------|
| 2024-05-09 | 08:50:48 | 2024-05-09 08:50:57.891 |
+------------+----------+-------------------------+

The following examples show date truncation. In all cases, the returned value is of the same data type as the input value, but with zeros for the portions, such as fractional seconds, that were truncated:

Truncate a date down to the year, month, and day:

SELECT mydate AS "DATE",
       TRUNC(mydate, 'year') AS "TRUNCATED TO YEAR",
       TRUNC(mydate, 'month') AS "TRUNCATED TO MONTH",
       TRUNC(mydate, 'day') AS "TRUNCATED TO DAY"
  FROM test_date_trunc;

Copy

+------------+-------------------+--------------------+------------------+
| DATE       | TRUNCATED TO YEAR | TRUNCATED TO MONTH | TRUNCATED TO DAY |
|------------+-------------------+--------------------+------------------|
| 2024-05-09 | 2024-01-01        | 2024-05-01         | 2024-05-09       |
+------------+-------------------+--------------------+------------------+

Truncate a time down to the minute:

SELECT mytime AS "TIME",
       TRUNC(mytime, 'minute') AS "TRUNCATED TO MINUTE"
  FROM test_date_trunc;

Copy

+----------+---------------------+
| TIME     | TRUNCATED TO MINUTE |
|----------+---------------------|
| 08:50:48 | 08:50:00            |
+----------+---------------------+

Truncate a TIMESTAMP down to the hour, minute, and second:

SELECT mytimestamp AS "TIMESTAMP",
       TRUNC(mytimestamp, 'hour') AS "TRUNCATED TO HOUR",
       TRUNC(mytimestamp, 'minute') AS "TRUNCATED TO MINUTE",
       TRUNC(mytimestamp, 'second') AS "TRUNCATED TO SECOND"
  FROM test_date_trunc;

Copy

+-------------------------+-------------------------+-------------------------+-------------------------+
| TIMESTAMP               | TRUNCATED TO HOUR       | TRUNCATED TO MINUTE     | TRUNCATED TO SECOND     |
|-------------------------+-------------------------+-------------------------+-------------------------|
| 2024-05-09 08:50:57.891 | 2024-05-09 08:00:00.000 | 2024-05-09 08:50:00.000 | 2024-05-09 08:50:57.000 |
+-------------------------+-------------------------+-------------------------+-------------------------+

Contrast the DATE_TRUNC function with the EXTRACT function:

SELECT TRUNC(mytimestamp, 'quarter') AS "TRUNCATED",
       EXTRACT('quarter', mytimestamp) AS "EXTRACTED"
  FROM test_date_trunc;

Copy

+-------------------------+-----------+
| TRUNCATED               | EXTRACTED |
|-------------------------+-----------|
| 2024-04-01 00:00:00.000 |         2 |
+-------------------------+-----------+