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