Summary: in this tutorial, you will learn how to use the SQL DATEADD() function to add an interval to a date.
Syntax
To add an interval e.g., a year, a month and a day to date, you use the SQL DATEADD() function. The following illustrates its syntax:
DATEADD (datepart , number , date )
Code language: SQL (Structured Query Language) (sql)Note that only SQL Server supports DATEADD() function.
Arguments
datepart
The datepart argument is the part of the date to which an interval is added. The following table shows the valid datepart:
| Valid Date Part | 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 |
number
The number is an integer constant or an expression that evaluates to an integer which function add to the datepart of date.
date
The date is the date to which the interval to be added. It can be a literal or an expression that evaluates to a DATE or DATETIME value.
Return types
The DATEADD() function returns the data type that is the same as the data type of the date argument.
Examples
The following example adds one year to a date:
--- add 1 year to a date
SELECT DATEADD(year, 1, '2018-01-01');
Code language: SQL (Structured Query Language) (sql)The result is:
2019-01-01 00:00:00.000
Code language: SQL (Structured Query Language) (sql)The following example adds two months to a date:
-- add 2 months to a date
SELECT DATEADD(month, 2, '2018-01-01');
Code language: SQL (Structured Query Language) (sql)Here is the result:
2018-03-01 00:00:00.000Code language: SQL (Structured Query Language) (sql)The following example shows how to add 30 days to a date:
-- add 30 days to a date
SELECT DATEADD(day, 30, '2018-01-01');
Code language: SQL (Structured Query Language) (sql)And here is the result:
2018-01-31 00:00:00.000
Code language: SQL (Structured Query Language) (sql)Besides adding an interval to a date, you can use the DATEADD() function to subtract an interval from a date. See the following example:
SELECT DATEADD(day,-1,'2018-01-01');
Code language: SQL (Structured Query Language) (sql)In this example, we subtracted one day from a date of January 1st, 2018 that returns the following result:
2017-12-31 00:00:00.000
Code language: SQL (Structured Query Language) (sql)In this tutorial, you have learned how to use the SQL DATEADD() function to add an interval to a date.
