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

trunc

This function is used to reset a date to a specific format.

Resetting means returning to default values, where the default values for year, month, and day are 01, and the default values for hour, minute, second, and millisecond are 00.

Syntax

trunc(string date, string format)

Parameters

Table 1 Parameters

Parameter

Mandatory

Type

Description

date

Yes

DATE or STRING

Date that needs to be processed

The following formats are supported:
  • yyyy-mm-dd
  • yyyy-mm-dd hh:mi:ss
  • yyyy-mm-dd hh:mi:ss.ff3

format

Yes

STRING

Format of the date to be converted

The value is a combination of the time unit (year, month, day, hour, minute, and second) and any character.

  • 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 the millisecond.

Return Values

The return value is of the DATE type, in the yyyy-mm-dd format.

  • 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 date is NULL, NULL is returned.
  • If the value of format is NULL, NULL is returned.

Example Code

The value 2023-08-01 is returned.

select trunc('2023-08-16', 'MM');

The value 2023-08-01 is returned.

select trunc('2023-08-16 10:54:36', 'MM');

The value NULL is returned.

 select trunc(null, 'MM');
Parent topic: Date Functions