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

datepart

This function is used to calculate the value that meets the specified datepart in date.

Syntax

datepart (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 BIGINT 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 NULL, NULL is returned.

Example Code

The value 2023 is returned.

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

The value 2023 is returned.

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

The value 0 is returned.

select datepart(date '2023-08-14 17:00:00', 'mi');

The value NULL is returned.

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