SQL Server DATEADD() Function

The DATEADD() function in SQL Server lets you easily add or subtract a specified time interval to or from a date value. This is incredibly useful for calculating future or past dates, creating date ranges, and manipulating temporal data within your SQL queries.

DATEADD(): Definition and Usage

DATEADD() provides a flexible way to adjust dates by adding or subtracting various time units, such as years, months, days, hours, minutes, seconds, and milliseconds. You can move forward or backward in time by using positive or negative values for the number of intervals.

Syntax

Syntax

DATEADD(interval, number, date)

Parameter Values

Parameter Description
interval The time/date interval to add. Options:
  • year, yyyy, yy: Year
  • quarter, qq, q: Quarter
  • month, mm, m: Month
  • dayofyear, dy, y: Day of year
  • day, dd, d: Day
  • week, ww, wk: Week
  • weekday, dw, w: Weekday
  • hour, hh: Hour
  • minute, mi, n: Minute
  • second, ss, s: Second
  • millisecond, ms: Millisecond
This is required.
number The number of intervals to add (positive) or subtract (negative). This is required.
date The date to modify. This is required.

Examples

Adding One Year to a Date

Adding one year to August 25th, 2017.

Syntax

SELECT DATEADD(year, 1, '2017/08/25') AS DateAdd;
Output

2018-08-25

Adding Two Months to a Date

Adding two months to August 25th, 2017.

Syntax

SELECT DATEADD(month, 2, '2017/08/25') AS DateAdd;
Output

2017-10-25

Subtracting Two Months from a Date

Subtracting two months from August 25th, 2017.

Syntax

SELECT DATEADD(month, -2, '2017/08/25') AS DateAdd;
Output

2017-06-25

Adding Years to a Date Column

Adding 18 years to the 'BirthDate' column in the 'Employees' table (assuming such a table exists with 'LastName' and 'BirthDate' columns).

Syntax

SELECT LastName, BirthDate, DATEADD(year, 18, BirthDate) AS DateAdd FROM Employees;
Output

LastName | BirthDate  | DateAdd
-------------------------------
(Results will vary depending on the data in your Employees table)