DATE_TRUNC

DATE_TRUNC(precision, source) truncates the datetime value source to the start of the time unit specified by precision.

Syntax

DATE_TRUNC(precision, source)

Parameters

Supported values of precision

Return value

The truncated datetime value of the same type as source. If source is not a datetime value, it is interpreted as a DATETIME value and returned as a DATETIME value.

Considerations

• If the value of precision is not in the preceding list (for example, weeks or months), it is treated as an invalid time unit and an error is returned.
• The value of precision is case-insensitive (for example, MONTH, Month, and month are equivalent).

Examples

1. Truncate the timestamp to the start of the month.

   obclient> SELECT DATE_TRUNC('month', TIMESTAMP '2026-03-24 14:30:00') AS d;
   

   The result is as follows:

   +---------------------+
   | d                   |
   +---------------------+
   | 2026-03-01 00:00:00 |
   +---------------------+
   1 row in set
   

2. Truncate to the start of the quarter.

   obclient> SELECT DATE_TRUNC('quarter', TIMESTAMP '2026-08-15 09:00:00') AS d;
   

   The result is as follows:

   +---------------------+
   | d                   |
   +---------------------+
   | 2026-07-01 00:00:00 |
   +---------------------+
   1 row in set
   

3. Truncate to the start of the ISO week (Monday).

   obclient> SELECT DATE_TRUNC('week', TIMESTAMP '2026-04-29 10:00:00') AS d;
   

   The result is as follows:

   +---------------------+
   | d                   |
   +---------------------+
   | 2026-04-27 00:00:00 |
   +---------------------+
   1 row in set
   

4. Return the DATE value in its original type (it is not forcibly converted to DATETIME).

   obclient> SELECT DATE_TRUNC('month', DATE '2026-03-24') AS d;
   

   The result is as follows:

   +------------+
   | d          |
   +------------+
   | 2026-03-01 |
   +------------+
   1 row in set