DATETRUNC function to truncate date values to a specified time unit - MaxCompute

DATETRUNC truncates a date or time value to a specified time unit and returns the truncated value.

Truncation is not the same as extraction. Truncating a DATETIME to the quarter returns the timestamp of the first day of that quarter (e.g., 2024-10-01 00:00:00). Extracting the quarter using EXTRACT returns the quarter number (e.g., 4).

Syntax

DATETRUNC supports two signatures.

Signature 1 — for DATE, DATETIME, and TIMESTAMP_NTZ inputs:

DATE|DATETIME|TIMESTAMP_NTZ DATETRUNC(DATE|DATETIME|TIMESTAMP_NTZ <date>, STRING <date_part>)

-- Returns 2025-01-01 00:00:00.
SELECT DATETRUNC(DATETIME '2025-12-07 16:28:46', 'yyyy');

Signature 2 — for TIMESTAMP inputs, with optional time zone conversion:

TIMESTAMP DATETRUNC(TIMESTAMP <date>, STRING <date_part>[, STRING <time_zone>])

-- The session time zone is Asia/Shanghai.
-- Returns 2025-01-01 01:00:00.
SELECT DATETRUNC(TIMESTAMP '2025-03-27 16:28:46', 'quarter', 'Asia/Jakarta');

Signature 2 converts the TIMESTAMP to the specified time_zone, truncates it, and returns the result in the session or project time zone.

Parameters

date

Required. The date or timestamp to truncate. Supported types: DATE, DATETIME, TIMESTAMP, TIMESTAMP_NTZ.

If your MaxCompute project uses the V1.0 data type edition, a STRING that matches the DATETIME format yyyy-mm-dd hh:mi:ss is also accepted. The function implicitly converts it to DATETIME before truncating.

date_part

Required. The time unit to truncate to. Must be a STRING constant.

Time unit	Value	Truncation behavior
Year	`year` or `yyyy`	Returns the first day of the year; time is set to `00:00:00`
Quarter	`quarter` or `q`	Returns the first day of the quarter; time is set to `00:00:00`
Month	`month`, `mon`, or `mm`	Returns the first day of the month; time is set to `00:00:00`
Week	`week` or `week(monday)`	Returns the Monday of the week; time is set to `00:00:00`
Week (custom start)	`week(weekday)`	Returns the specified weekday of the week; time is set to `00:00:00`. Valid values: `SUNDAY`, `MONDAY`, `TUESDAY`, `WEDNESDAY`, `THURSDAY`, `FRIDAY`, `SATURDAY`
ISO week	`isoweek`	Returns the Monday of the ISO 8601 week; time is set to `00:00:00`
Day	`day` or `dd`	Keeps the date; time is set to `00:00:00`
Hour	`hour` or `hh`	Keeps up to the hour; minutes and seconds are set to `00:00`
Minute	`mi`	Keeps up to the minute; seconds are set to `00`
Second	`ss`	Keeps up to the second; sub-second fractions are removed
Millisecond	`ff3`	Keeps up to millisecond precision

time_zone

Optional. Signature 2 only. A STRING specifying the time zone in which the TIMESTAMP is converted before truncation. If omitted, the session or project time zone is used.

If the project time zone has not been changed, the default is China Standard Time (UTC+08:00).

Return value

Returns the same data type as date. The following edge cases apply:

Condition	Result
`date` is not DATE, DATETIME, TIMESTAMP, or TIMESTAMP_NTZ	Error
`date` is NULL	Error
`date_part` is NULL	NULL
`date_part` is non-constant, an unsupported format, or not a STRING	Error

Examples

Basic examples

The following examples use Signature 1 with DATE, DATETIME, and TIMESTAMP_NTZ inputs.

-- Returns 2024-01-01 00:00:00.
SELECT DATETRUNC(DATETIME'2024-12-07 16:28:46', 'yyyy');

-- Returns 2024-12-01 00:00:00.
SELECT DATETRUNC(DATETIME'2024-12-07 16:28:46', 'MONTH');

-- Returns 2024-12-02.
SELECT DATETRUNC(DATE'2024-12-07', 'week(monday)');

-- Returns 2024-10-01 00:00:00.
SELECT DATETRUNC(TIMESTAMP_NTZ'2024-12-07 16:28:46', 'q');

-- Returns 2024-12-07 16:28:46.
SELECT DATETRUNC(TIMESTAMP_NTZ'2024-12-07 16:28:46.123', 'ss');

-- Returns 2024-12-07 16:28:46.123.
SELECT DATETRUNC(TIMESTAMP_NTZ'2024-12-07 16:28:46.123456', 'ff3');

-- Returns NULL.
SELECT DATETRUNC(DATE'2024-12-07', NULL);

To see how different date_part values affect the same input, run them side by side:

SELECT
  DATETRUNC(DATETIME'2024-12-07 16:28:46', 'yyyy')    AS truncated_to_year,
  DATETRUNC(DATETIME'2024-12-07 16:28:46', 'quarter')  AS truncated_to_quarter,
  DATETRUNC(DATETIME'2024-12-07 16:28:46', 'month')    AS truncated_to_month,
  DATETRUNC(DATETIME'2024-12-07 16:28:46', 'week')     AS truncated_to_week,
  DATETRUNC(DATETIME'2024-12-07 16:28:46', 'day')      AS truncated_to_day,
  DATETRUNC(DATETIME'2024-12-07 16:28:46', 'hour')     AS truncated_to_hour;

Output:

truncated_to_year	truncated_to_quarter	truncated_to_month	truncated_to_week	truncated_to_day	truncated_to_hour
2024-01-01 00:00:00	2024-10-01 00:00:00	2024-12-01 00:00:00	2024-12-02 00:00:00	2024-12-07 00:00:00	2024-12-07 16:00:00

Examples with time_zone

When date is a TIMESTAMP, use time_zone to specify the time zone in which truncation is applied. The result is returned in the session or project time zone.

-- Set the session time zone to Asia/Shanghai.
SET odps.sql.timezone=Asia/Shanghai;

-- Returns 2024-01-01 00:00:00.
SELECT DATETRUNC(TIMESTAMP '2024-12-07 16:28:46', 'yyyy');

-- Returns 2025-01-01 01:00:00.
SELECT DATETRUNC(TIMESTAMP '2025-03-27 16:28:46', 'quarter', 'Asia/Jakarta');

-- Returns 2025-03-21 01:00:00.
SELECT DATETRUNC(TIMESTAMP '2025-03-27 16:28:46', 'week(friday)', 'Asia/Jakarta');

-- Returns 2025-03-24 08:00:00.
SELECT DATETRUNC(TIMESTAMP '2025-03-27 16:28:46', 'isoweek', 'Etc/GMT');

-- Returns 2025-11-07 01:00:00.
SELECT DATETRUNC(TIMESTAMP '2025-11-07 10:30:00', 'dd', 'Asia/Jakarta');

-- Returns 2025-11-07 10:00:00.
SELECT DATETRUNC(TIMESTAMP '2025-11-07 10:30:00', 'hour', 'Asia/Jakarta');

-- Returns 2025-11-07 10:30:00.
SELECT DATETRUNC(TIMESTAMP '2025-11-07 10:30:00', 'mi', 'Asia/Jakarta');

STRING input (V1.0 data type edition)

When the V1.0 data type edition is active, a STRING that matches yyyy-mm-dd hh:mi:ss is implicitly converted to DATETIME. Strings with sub-second fractions do not match the format and return NULL.

-- Set the data type edition to V1.0.
SET odps.sql.type.system.odps2=false;
SET odps.sql.hive.compatible=false;

-- Returns NULL. The string includes milliseconds, which do not match the DATETIME format.
SELECT DATETRUNC('2025-07-27 16:28:46.123', 'mi');

-- Returns 2025-07-27 16:28:00. The string matches the DATETIME format.
SELECT DATETRUNC('2025-07-27 16:28:46', 'mi');

Related functions

DATETRUNC is a date function. For date calculation and conversion functions, see Date functions.