Summary: in this tutorial, you will learn how to use the SQL DATEADD() function to add an interval to a date.
Note that the DATEADD function is not included in SQL Standard and only SQL Server supports it.
Introduction to the SQL DATEADD Function #
To add an interval e.g., a year, a month and a day to date, you use the SQL DATEADD() function.
Here’s the syntax of the DATEADD function:
DATEADD (datepart , number , date )Code language: SQL (Structured Query Language) (sql)In this syntax:
datepartis the part of the date to which an interval is added.numberis a literal integer or an expression that evaluates to an integer which function adds to thedatepartofdate.dateis the date to which the function add the interval. It can be a literal or an expression that evaluates to aDATEorDATETIMEvalue.
The DATEADD() function returns the data type that is the same as the data type of the date argument.
The following table shows the valid values for the 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 |
SQL DATEADD Function Examples #
The following query uses the DATEADD() function to add one year to a date:
SELECT DATEADD(year, 1, '2018-01-01');Code language: SQL (Structured Query Language) (sql)Output:
2019-01-01 00:00:00.000Code language: SQL (Structured Query Language) (sql)The following example uses the DATEADD() function to add two months to a date:
SELECT DATEADD(month, 2, '2018-01-01');Code language: SQL (Structured Query Language) (sql)Output:
2018-03-01 00:00:00.000Code language: SQL (Structured Query Language) (sql)The following example uses the DATEADD() fucntion to add 30 days to a date:
SELECT DATEADD(day, 30, '2018-01-01');Code language: SQL (Structured Query Language) (sql)Output:
2018-01-31 00:00:00.000Code 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. For example:
SELECT DATEADD(day,-1,'2018-01-01');Code language: SQL (Structured Query Language) (sql)In this example, we subtract one day from a date of January 1st, 2018 that returns the following result:
2017-12-31 00:00:00.000Code language: SQL (Structured Query Language) (sql)Summary #
- Use the SQL
DATEADD()function to add an interval to a date.
