DATEADD

SQL Server

DATEADD(datepart , number , date )

• datepart: This parameter identifies the part of the date that the function will modify. It can include values like year, quarter, month, day, etc. It decides which part of the date or time data to add to or subtract from.
• number: This is the value to be added to the date. It can take a positive or a negative integer, where a positive value increments, a negative value decrements the date value.
• date: This is the starting date. The function adds or subtracts the value of the ‘number’ parameter from this date. It can be a date or datetime.

Example

SELECT DATEADD(year, 2, '2022-01-01') AS NewDate

Output

NewDate
-------------
2024-01-01

Explanation

The DATEADD function has been used to add 2 years to a specified date (2022-01-01). The output is a new date (2024-01-01).

Oracle

DATEADD(unit, increment, date)

• unit: This is the part of the date/time value that will be manipulated by the DATEADD function. It can refer to the year, month, day, hour, minute, second, week, etc.
• increment: This parameter determines the quantity that will be added to the specified part of the date/time value. It must be an integer. Positive values result in a future date/time, while negative values result in a past date/time.
• date: This is the original date/time value that will be adjusted by the DATEADD function. It can be a date literal, a constant, a variable or a column of a table.

Example

SELECT DATEADD(day, 2, TO_DATE('2022-07-01', 'YYYY-MM-DD')) FROM dual;

Output

2022-07-03

Explanation

In the given example, DATEADD function is used to add 2 days to the date ‘2022-07-01’. The output ‘2022-07-03’ represents date 2 days later. (Note: Oracle does not have a DATEADD function, the above functionality can be achieved by using date + n in Oracle SQL)