T-SQL Tutorial

DATEADD Date Function

Dateadd is an date function and returns a date with the specified number interval added.
The DATEADD function in SQL Server is used to add a specified number of units (e.g. days, months, years) to a given date.

The SQL DATEADD function takes three arguments: the unit of measure to add (e.g. day, month, year), the number of units to add, and the date to which the units will be added. The function returns the modified date.


The syntax for using DATEADD is as follows:

DATEADD (interval, number , date)

The interval argument specifies the type of time interval to be added or subtracted. For example, you can use 'day' to add or subtract a number of days, 'month' to add or subtract a number of months, or 'minute' to add or subtract a number of minutes. The full list of possible intervals can be found in the SQL Server documentation.
The number argument specifies the number of intervals to add or subtract. This can be a positive or negative integer, depending on whether you want to add or subtract time.
The date argument specifies the original date to which the interval is to be added or subtracted.


Here's an example of how to use the DATEADD function in SQL Server:

SELECT DATEADD(day, 7, '2023-03-23') AS NewDate

This query will add 7 days to the date '2023-03-23', resulting in the output: 2023-03-30.

More examples

Add 2 years to the date variable.

DECLARE @date datetime = '2014-01-01 13:10:10';
SELECT 'add_year' as Add_year, DATEADD(year,2,@date) as Result;

add_year2016-01-01 13:10:10.000


DATEADD(month, 2, SYSDATETIME()) as Add_month,
DATEADD(year, 2, SYSDATETIME())as Add_year;

2014-02-21 21:52:13.27456892014-04-19 21:52:13.27456892016-02-19 21:52:13.2745689

You can also use DATEADD to subtract units of time by providing a negative value as the second argument.
SELECT DATEADD(month, -3, '2022-01-01');
This will return the date 2021-10-01.

The DATEADD function is a useful tool for performing date calculations and manipulating dates in SQL Server. It can be used in a variety of scenarios, such as calculating the expiration date for a product or calculating the due date for a task.