Overview

Updated on 2025-01-22 GMT+08:00

View PDF

Table 1 lists the date functions supported by DLI.

**Table 1** Date/time functions
Syntax	Value Type	Description
add_months(string start_date, int num_months)	STRING	Returns the date that is num_months after start_date.
current_date()	DATE	Returns the current date, for example, 2016-07-04.
current_timestamp()	TIMESTAMP	Returns a timestamp of the TIMESTAMP type.
date_add(string startdate, int days)	STRING or DATE	Adds a number of days to a date.
dateadd(string date, bigint delta, string datepart)	STRING or DATE	Changes a date based on datepart and delta. date: This parameter is mandatory. Date value, which is of the STRING type. The time format is yyyy-mm-dd hh:mi:ss, for example, 2021-08-28 00:00:00. delta: This parameter is mandatory. Adjustment amplitude, which is of the BIGINT type. datepart: Adjustment unit, which is a constant of the STRING type. This parameter is mandatory.
date_sub(string startdate, int days)	STRING	Subtracts a number of days from a date.
date_format(string date, string format)	STRING	Converts a date into a string based on the format specified by format.
datediff(string date1, string date2)	BIGINT	Calculates the difference between date1 and date2.
datediff1(string date1, string date2, string datepart)	BIGINT	Calculates the difference between date1 and date2 and returns the difference in a specified datepart.
datepart (string date, string datepart)	BIGINT	Returns the value of the field that meets a specified time unit in the date.
datetrunc (string date, string datepart)	STRING	Calculates the date otained through the truncation of a specified date based on a specified datepart. date: The value is in the yyyy-mm-dd or yyyy-mm-dd hh:mi:ss format. This parameter is mandatory. datepart: Supported date format, which is a STRING constant. This parameter is mandatory.
day(string date), dayofmonth(string date)	INT	Returns the date of a specified time.
from_unixtime(bigint unixtime)	STRING	Converts a timestamp to a time, in yyyy-MM-dd HH:mm:ss or yyyyMMddHHmmss.uuuuuu format. For example, select FROM_UNIXTIME(1608135036,'yyyy-MM-dd HH:mm:ss').
from_utc_timestamp(string timestamp, string timezone)	TIMESTAMP	Converts a UTC timestamp to the corresponding timestamp in a specific time zone.
getdate()	STRING	Obtains the current system time.
hour(string date)	INT	Returns the hour (from 0 to 23) of a specified time.
isdate(string date , string format)	BOOLEAN	date: Date, which is implicitly converted to the STRING type and then used for calculation. This parameter is mandatory. format: Unsupported date extension format, which is a STRING constant. This parameter is mandatory. If there are redundant format strings in format, only the date value corresponding to the first format string is used. Other format strings are used as separators. For example, isdate("1234-yyyy", "yyyy-yyyy") returns True.
last_day(string date)	DATE	Returns the last day of the month a date belongs to, in the format of yyyy-MM-dd, for example, 2015-08-31.
lastday(string date)	STRING	Returns the last day of the month a date belongs to. The value is of the STRING type and is in the yyyy-mm-dd hh:mi:ss format.
minute(string date)	INT	Returns the minute (from 0 to 59) of a specified time.
month(string date)	INT	Returns the month (from January to December) of a specified time.
months_between(string date1, string date2)	DOUBLE	Returns the month difference between date1 and date2.
next_day(string start_date, string day_of_week)	DATE	Returns the date closest to day_of_week after start_date, in the yyyy-MM-dd format. day_of_week indicates a day in a week, for example, Monday or Friday.
quarter(string date)	INT	Returns the quarter of the date, timestamp, or string, for example, quarter('2015-04-01')=2.
second(string date)	INT	Returns the second (from 0 to 59) of a specified time.
to_char(string date, string format)	STRING	Converts a date into a string in a specified format.
to_date(string timestamp)	DATE	Returns the date part of a time string, for example, to_date("1970-01-01 00:00:00") = "1970-01-01".
to_date1(string date, string format)	STRING	The value is of the STRING type, in the yyyy-mm-dd hh:mi:ss format. If the value of date or format is NULL, NULL is returned. Converts a string in a specified format to a date value.
to_utc_timestamp(string timestamp, string timezone)	TIMESTAMP	Converts a timestamp in a given time zone to a UTC timestamp.
trunc(string date, string format)	DTAE	Resets the date in a specified format. Supported formats are MONTH/MON/MM and YEAR/YYYY/YY, for example, trunc('2015-03-17', 'MM') = 2015-03-01.
unix_timestamp(string timestamp, string pattern)	BIGINT	Returns a Unix timestamp (the number of seconds since 1970-01-01 00:00:00) as an unsigned integer if the function is called without parameters.
weekday (string date)	INT	Returns the day of the current week.
weekofyear(string date)	INT	Returns the week number (from 0 to 53) of a specified date.
year(string date)	INT	Returns the year of a specified date.

Parent topic: Date Functions

Previous topic: Date Functions

Next topic: add_months

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot