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¶
Parameter | Mandatory | Type | Description |
|---|---|---|---|
date | Yes | DATE or STRING | Start date The following formats are supported:
|
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.
|
Return Values¶
The return value is of the DATE or STRING type.
Note
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);