DATEADD() Function in SQL Server

DATEADD() function :
This function in SQL Server is used to sum up a time or a date interval to a specified date, then returns the modified date.

Features :

• This function is used to sum up a time or a date interval to a date specified.
• This function comes under Date Functions.
• This function accepts three parameters namely interval, number and date.
• This function can also include time in the interval section.

Syntax :

DATEADD(interval, number, date)

Parameter :
This method accepts three parameters as given below as follows.

• interval –
  It is the specified time or date interval which is to be added. Moreover, the values of the interval can be as given below.

  year, yyyy, yy   = Year, which is the specified year to be added.
  quarter, qq, q   = Quarter, which is the specified quarter to be added.
  month, mm, m     = month, which is the specified month to be added.
  dayofyear, dy, y = Day of the year, which is the specified day of the year to be added.
  day, dd, d       = Day, which is the specified day to be added.
  week, ww, wk     = Week, which is the specified week to be added.
  weekday, dw, w   = Weekday, which is the specified week day to be added.
  hour, hh         = hour, which is the specified hour to be added.
  minute, mi, n    = Minute, which is the specified minute to be added.
  second, ss, s    = Second, which is the specified second to be added.
  millisecond, ms  = Millisecond, which is the specified millisecond to be added.
  

• number –
  It is the number of interval which is to be added to the date specified. It can be positive, in order to get the dates of the future or it can be negative also, in order to get the dates in the past.
• date –
  It is the specified date which is to be altered.

Returns :
It returns a modified date after adding a date or time interval to the stated date.

Example-1 :
Using DATEADD() function and adding the year part of the date for getting the modified date.

SELECT DATEADD(year, 2, '2019/01/05');

Output :

2021-01-05 00:00:00.000

Example-2 :
Using DATEADD() function and adding the month part of the date for getting the modified date.

SELECT DATEADD(month, 11, '2019/01/05');

Output :

2019-12-05 00:00:00.000

Example-3 :
Using DATEADD() function and subtracting the month part of the date for getting the modified date.

SELECT DATEADD(month, -1, '2019/01/05');

Output :

2018-12-05 00:00:00.000

Example-4 :
Using DATEADD() function and adding the day part of the date for getting the modified date.

SELECT DATEADD(day, 32, '2015/04/14');

Output :

2015-05-16 00:00:00.000

Example-5 :
Using DATEADD() function and adding the minute part of the date for getting the modified date.

SELECT DATEADD(minute, 6, '2015/04/14 09:55');

Output :

2015-04-14 10:01:00.000

Example-6 :
Using DATEADD() function and adding the hour part of the date using a variable for getting the modified date.

DECLARE @number INT;
SET @number = 8;
SELECT 
DATEADD(hh, @number, '2021/01/02 08:50');

Output :

2021-01-02 16:50:00.000

Example-7 :
Using DATEADD() function and adding the second part of the date using variables for getting the modified date.

DECLARE @number INT;
DECLARE @date VARCHAR(50);
SET @number = 08;
SET @date = '2011/11/22 07:59:56';
SELECT 
DATEADD(ss, @number, @date);

Output :

2011-11-22 08:00:04.000

Application :
This function is used to find the modified date after adding a date or time interval to the stated date.