MaxCompute:DATETRUNC

Syntax

This function supports the following two signatures.

• Signature 1: Truncates a DATE, DATETIME, or TIMESTAMP_NTZ value to the specified time unit date_part and returns the truncated value.

  DATE|DATETIME|TIMESTAMP_NTZ DATETRUNC(DATE|DATETIME|TIMESTAMP_NTZ <date>, STRING <date_part>)
  
  -- Standard example.
  -- Returns 2025-01-01 00:00:00.
  SELECT DATETRUNC(DATETIME '2025-12-07 16:28:46', 'yyyy');

• Signature 2: Converts a TIMESTAMP value to a specified time zone, truncates the value to the specified time unit date_part, and then returns a time value in the time zone of the current session or project.

  TIMESTAMP DATETRUNC(TIMESTAMP <date>,STRING <date_part>[, STRING <time_zone>])
  
  -- Standard example.
  -- The time zone of the current session or project is Asia/Shanghai.
  -- Returns 2025-01-01 01:00:00.
  SELECT DATETRUNC(TIMESTAMP '2025-03-27 16:28:46', 'quarter', 'Asia/Jakarta');

Parameters

• date: Required. A date or timestamp. The supported data types are DATE, DATETIME, TIMESTAMP, and TIMESTAMP_NTZ.

  If your MaxCompute project uses the V1.0 data type edition, the input parameter can be a STRING. The string must match the DATETIME format, which is yyyy-mm-dd hh:mi:ss, such as 2025-07-21 00:00:00. The function implicitly converts the string to the DATETIME type for the calculation.

• date_part: Required. The time unit for truncation. This parameter must be a STRING constant. The following values are supported:

  Time unit	Value
  Year	year or yyyy
  Quarter	quarter or q
  Month	month, mon, or mm
  Week	week: A week starts on Monday. This is equivalent to week(monday). week(weekday): A week starts on the specified day of the week (weekday). Valid values: SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, and SATURDAY.
  ISO week	isoweek uses the week boundaries defined in ISO 8601. An ISO week starts on Monday.
  Day	day or dd
  Hour	hour or hh
  Minute	mi
  Second	ss
  Millisecond	ff3

• time_zone: Optional. A STRING that specifies the time zone. If you do not specify this parameter, the time zone of the current session or project is used by default.

  Note

  If you have not changed the project's time zone, the default is China Standard Time (UTC+08:00).

Return value

Examples

• Example 1: Basic examples

  -- 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);

• Example 2: Use the time_zone parameter to specify a time zone

  If the date parameter is a TIMESTAMP type, you can use the time_zone parameter to specify a time zone. The function returns a time value in the time zone of the current session or project.

  -- 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');

• Example 3: The date parameter is a STRING type

  If your MaxCompute project uses the V1.0 data type edition, and the date parameter is a STRING that matches the DATETIME format yyyy-mm-dd hh:mi:ss, the string is implicitly converted to the DATETIME type for the calculation.

  -- Set the data type edition to V1.0.
  SET odps.sql.type.system.odps2=false;
  SET odps.sql.hive.compatible=false;
  
  -- Incorrect example: The following input parameter is a STRING but does not match the DATETIME format. The function returns NULL.
  -- Returns NULL.
  SELECT DATETRUNC('2025-07-27 16:28:46.123','mi');
  
  -- Correct example: The following input parameter is a STRING that matches the DATETIME format.
  -- Returns 2025-07-27 16:28:00.
  SELECT DATETRUNC('2025-07-27 16:28:46', 'mi');

Related functions

DATETRUNC is a date function. For more information about functions for date calculations and conversions, see Date functions.