Overview

Updated on 2024-07-04 GMT+08:00

View PDF

Table 1 lists the date functions supported by DLI.

**Table 1** Date/time functions
Function	Syntax	Value Type	Description
add_months	add_months(string start_date, int num_months)	STRING	Returns the date that is num_months after start_date.
current_date	current_date()	DATE	Returns the current date, for example, 2016-07-04.
current_timestamp	current_timestamp()	TIMESTAMP	Returns a timestamp of the TIMESTAMP type.
date_add	date_add(string startdate, int days)	STRING or DATE	Adds a number of days to a date.
dateadd	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	date_sub(string startdate, int days)	STRING	Subtracts a number of days from a date.
date_format	date_format(string date, string format)	STRING	Converts a date into a string based on the format specified by format.
datediff	datediff(string date1, string date2)	BIGINT	Calculates the difference between date1 and date2.
datediff1	datediff1(string date1, string date2, string datepart)	BIGINT	Calculates the difference between date1 and date2 and returns the difference in a specified datepart.
datepart	datepart (string date, string datepart)	BIGINT	Returns the value of the field that meets a specified time unit in the date.
datetrunc	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/dayofmonth	day(string date), dayofmonth(string date)	INT	Returns the date of a specified time.
from_unixtime	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	from_utc_timestamp(string timestamp, string timezone)	TIMESTAMP	Converts a UTC timestamp to the corresponding timestamp in a specific time zone.
getdate	getdate()	STRING	Obtains the current system time.
hour	hour(string date)	INT	Returns the hour (from 0 to 23) of a specified time.
isdate	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	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	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	minute(string date)	INT	Returns the minute (from 0 to 59) of a specified time.
month	month(string date)	INT	Returns the month (from January to December) of a specified time.
months_between	months_between(string date1, string date2)	DOUBLE	Returns the month difference between date1 and date2.
next_day	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	quarter(string date)	INT	Returns the quarter of the date, timestamp, or string, for example, quarter('2015-04-01')=2.
second	second(string date)	INT	Returns the second (from 0 to 59) of a specified time.
to_char	to_char(string date, string format)	STRING	Converts a date into a string in a specified format.
to_date	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	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	to_utc_timestamp(string timestamp, string timezone)	TIMESTAMP	Converts a timestamp in a given time zone to a UTC timestamp.
trunc	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	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	weekday (string date)	INT	Returns the day of the current week.
weekofyear	weekofyear(string date)	INT	Returns the week number (from 0 to 53) of a specified date.
year	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