Help Center/ Data Lake Insight/ Spark SQL Syntax Reference/ Built-In Functions/ Date Functions/ dateadd
Updated on 2024-08-19 GMT+08:00
View PDF

dateadd

This function is used to change a date based on datepart and delta.

To obtain the date with a specified change range based on the current date, use this function together with the current_date or getdate function.

Syntax

dateadd(string date, bigint delta, 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

delta

Yes

BIGINT

Amplitude, based on which the date is modified

datepart

Yes

BIGINT

Unit, based on which the date is modified

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.
  • 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 STRING 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 date is NULL, NULL is returned.
  • If the value of delta or datepart is NULL, NULL is returned.

Example Code

The value 2023-08-15 17:00:00 is returned after one day is added.

select dateadd( '2023-08-14 17:00:00', 1, 'dd');

The value 2025-04-14 17:00:00 is returned after 20 months are added.

select dateadd('2023-08-14 17:00:00', 20, 'mm');

The value 2023-09-14 17:00:00 is returned.

select dateadd('2023-08-14 17:00:00', 1, 'mm');

The value 2023-09-14 is returned.

select dateadd('2023-08-14', 1, 'mm');

If the current time is 2023-08-14 17:00:00, 2023-08-13 17:00:00 is returned.

select dateadd(getdate(),-1,'dd');

The value NULL is returned.

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