Help Center> Data Lake Insight> SQL Syntax Reference (To Be Offline)> Spark SQL Syntax Reference (Unavailable Soon)> Built-in Functions> Date Functions> datetrunc
Updated on 2023-10-25 GMT+08:00
View PDF

datetrunc

This function is used to calculate the date otained through the truncation of a specified date based on a specified datepart.

It truncates the date before the specified datepart and automatically fills the remaining part with the default value. For details, see Example Code.

Syntax

datetrunc (string date, string datepart)

Parameters

Table 1 Parameters

Parameter

Mandatory

Type

Description

date

Yes

DATE

or

STRING

Start date

The following formats are supported:

  • yyyy-mm-dd
  • yyyy-mm-dd hh:mi:ss
  • yyyy-mm-dd hh:mi:ss.ff3

datepart

Yes

STRING

Time unit of the value to be returned

This parameter supports the following extended date formats: year, month or mon, day, and hour.

  • YYYY or yyyy indicates the year.
  • MM indicates the month.
  • mm indicates the minute.
  • dd indicates the day.
  • HH indicates the 24-hour clock.
  • hh indicates the 12-hour clock.
  • mi indicates the minute.
  • ss indicates the second.
  • SSS indicates millisecond.

Return Values

The return value is of the DATE or STRING type.

  • If the value of date is not of the DATE or STRING type, the error message "data type mismatch" is displayed.
  • If the value of date is of the DATE or STRING type but is not in one of the supported formats, NULL is returned.
  • If the value of datepart is NULL, NULL is returned.
  • If the value of datepart is hour, minute, or second, the date is truncated to the day and returned.

Example Code

Example static data

The value 2023-01-01 00:00:00 is returned.

select datetrunc('2023-08-14 17:00:00', 'yyyy');

The value 2023-08-01 00:00:00 is returned.

select datetrunc('2023-08-14 17:00:00', 'month');

The value 2023-08-14 is returned.

select datetrunc('2023-08-14 17:00:00', 'DD');

The value 2023-01-01 is returned.

select datetrunc('2023-08-14', 'yyyy');

The value 2023-08-14 is returned.

select datetrunc('2023-08-14 17:00:00', 'HH');

The value NULL is returned.

select datetrunc('2023-08-14', null);
Parent topic: Date Functions