In SQL Server, you can use the DATEADD() function to add a specified time period to a given date. You can also use it to subtract a specified time period.

You can also combine DATEADD() with other functions to format the date as required. For example, you could take ‘2020-10-03’, add 10 years, then return the (increased) year component.

This article contains examples to demonstrate.

Syntax

The syntax of DATEADD() goes like this:

DATEADD (datepart , number , date )

Where datepart is the part of the date you want to be increased (or decreased), number is the amount to increase datepart by, and date is the date to which the addition will take place.

The datepart argument can be any of the following:

datepart	Abbreviations
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw, w
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns

Example 1

Here’s a basic example of adding ten years to a date:

SELECT DATEADD(year, 10, '2020-10-03') AS 'Future Date';

Result:

+-------------------------+
| Future Date             |
|-------------------------|
| 2030-10-03 00:00:00.000 |
+-------------------------+

In this case, the return value includes the time component as well as the date. This is because the result is returned as a datetime value. The reason it’s returned as this data type is because we supplied a string literal as the date argument. When you supply a string literal as the date, DATEADD() returns a datetime value.

In cases where you don’t supply a string literal, the return value is the same as the data type of the date argument. For example, if you provide a datetime2 argument, the return value will be datetime2.

Example 2 – Formatting the Result

We can take the above result and format it with the FORMAT() function:

SELECT 
    FORMAT(DATEADD(year, 10, '2020-10-03'), 'yyyy-MM-dd') AS 'yyyy-MM-dd',
    FORMAT(DATEADD(year, 10, '2020-10-03'), 'dd/MM/yyyy') AS 'dd/MM/yyyy',
    FORMAT(DATEADD(year, 10, '2020-10-03'), 'yyyy') AS 'yyyy',
    FORMAT(DATEADD(year, 10, '2020-10-03'), 'yy') AS 'yy';

Result:

+--------------+--------------+--------+------+
| yyyy-MM-dd   | dd/MM/yyyy   | yyyy   | yy   |
|--------------+--------------+--------+------|
| 2030-10-03   | 03/10/2030   | 2030   | 30   |
+--------------+--------------+--------+------+

But it’s important to note that the FORMAT() function returns its result as a string.

Another option is to use CONVERT() to convert the result to a date data type:

SELECT CONVERT(date, DATEADD(year, 10, '2020-10-03')) AS Converted;

Result:

+-------------+
| Converted   |
|-------------|
| 2030-10-03  |
+-------------+

Or you can use a function like YEAR(), which returns the result as an integer:

SELECT YEAR(DATEADD(year, 10, '2020-10-03')) AS 'Future Year';

Result:

+---------------+
| Future Year   |
|---------------|
| 2030          |
+---------------+

Example 3 – Subtracting Dates

You can use negative numbers to subtract from the date:

SELECT DATEADD(year, -10, '2020-10-03') AS 'Earlier Date';

Result:

+-------------------------+
| Earlier Date            |
|-------------------------|
| 2010-10-03 00:00:00.000 |
+-------------------------+

And of course, you can format this using any of the methods previously mentioned.

Example 4 – System Dates

Here are some examples of using various functions to return the current date/time from the computer on which the instance of SQL Server is running.

SYSDATETIME()

SELECT 
    SYSDATETIME() AS 'Current Date',
    DATEADD(year, 10, SYSDATETIME()) AS 'Future Date';

Result:

+-----------------------------+-----------------------------+
| Current Date                | Future Date                 |
|-----------------------------+-----------------------------|
| 2018-06-04 05:57:51.7297042 | 2028-06-04 05:57:51.7297042 |
+-----------------------------+-----------------------------+

SYSDATETIMEOFFSET()

SELECT 
    SYSDATETIME() AS 'Current Date',
    DATEADD(year, 10, SYSDATETIME()) AS 'Future Date';

Result:

+--------------------------+--------------------------+
| Current Date             | Future Date              |
|--------------------------+--------------------------|
| 4/6/18 6:02:07 am +00:00 | 4/6/28 6:02:07 am +00:00 |
+--------------------------+--------------------------+

As mentioned, these results are returned using the data type of the date argument (because they’re not string literals).

Formatting the Output

SELECT 
    YEAR(SYSDATETIME()) AS 'Current Year',
    YEAR(DATEADD(year, 10, SYSDATETIME())) AS 'Future Year';

Result:

+----------------+---------------+
| Current Year   | Future Year   |
|----------------+---------------|
| 2018           | 2028          |
+----------------+---------------+

And as also mentioned, if you format the date, it will be returned in the data type for the applicable function. So in this example, the result is returned as an int. If we’d formatted it with the FORMAT() function, it would be returned as a string.