By: Daniel Calbimonte
The DATEADD function returns a date with the addition of a specified part of the date. For example, if you add one year to the current date, it will return the date provided plus 1 year.
Syntax
DATEADD(datepart, numberToAdd, date)
Parameters
- datepart - This is the part of the date that we want to add a value. It can be a 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). You can use the full name or the abbreviations in the parenthesis.
- numberToAdd – Is the number to be added. This value will be truncated if it contains fractions. This can be a positive or negative value.
- date – This is the starting date that will be used to add a value. This can be a date or time or both.
Simple DATEADD Example
The following example will add 1 month to the current date.
SELECT CURRENT_TIMESTAMP, DATEADD(month, 1, CURRENT_TIMESTAMP) as dateadd
Using DATEADD with NULL Values
If a NULL value is used, the result will be NULL.
SELECT DATEADD(s, 1, NULL) as dateadd
DATEADD Examples Using All Options
The next example will show how to add a unit of specific dataparts we will show the full datepart name and abbreviations. We will use the below date for the examples.
DECLARE @date datetime2 = '2022-01-07 14:36:17.6222691'
| DateGroup | DatePart | Query | Result |
|---|---|---|---|
| day | d | SELECT DATEADD(d, 1, @date) | 2022-01-08 14:36:17.6222691 |
| day | day | SELECT DATEADD(day, 1, @date) | 2022-01-08 14:36:17.6222691 |
| day | dd | SELECT DATEADD(dd, 1, @date) | 2022-01-08 14:36:17.6222691 |
| dayofyear | dayofyear | SELECT DATEADD(dayofyear, 1, @date) | 2022-01-08 14:36:17.6222691 |
| dayofyear | dy | SELECT DATEADD(dy, 1, @date) | 2022-01-08 14:36:17.6222691 |
| dayofyear | y | SELECT DATEADD(y, 1, @date) | 2022-01-08 14:36:17.6222691 |
| hour | hh | SELECT DATEADD(hh, 1, @date) | 2022-01-07 15:36:17.6222691 |
| hour | hour | SELECT DATEADD(hour, 1, @date) | 2022-01-07 15:36:17.6222691 |
| microsecond | microsecond | SELECT DATEADD(microsecond, 1, @date) | 2022-01-07 14:36:17.6222701 |
| microsecond | mcs | SELECT DATEADD(mcs, 1, @date) | 2022-01-07 14:36:17.6222701 |
| millisecond | millisecond | SELECT DATEADD(millisecond, 1, @date) | 2022-01-07 14:36:17.6232691 |
| millisecond | ms | SELECT DATEADD(ms, 1, @date) | 2022-01-07 14:36:17.6232691 |
| minute | mi | SELECT DATEADD(mi, 1, @date) | 2022-01-07 14:37:17.6222691 |
| minute | minute | SELECT DATEADD(minute, 1, @date) | 2022-01-07 14:37:17.6222691 |
| minute | n | SELECT DATEADD(n, 1, @date) | 2022-01-07 14:37:17.6222691 |
| month | m | SELECT DATEADD(m, 1, @date) | 2022-02-07 14:36:17.6222691 |
| month | mm | SELECT DATEADD(mm, 1, @date) | 2022-02-07 14:36:17.6222691 |
| month | month | SELECT DATEADD(month, 1, @date) | 2022-02-07 14:36:17.6222691 |
| nanosecond | nanosecond | SELECT DATEADD(nanosecond, 1, @date) | 2022-01-07 14:36:17.6222691 |
| nanosecond | ns | SELECT DATEADD(ns, 1, @date) | 2022-01-07 14:36:17.6222691 |
| quarter | q | SELECT DATEADD(q, 1, @date) | 2022-04-07 14:36:17.6222691 |
| quarter | SELECT DATEADD(qq, 1, @date) | 2022-04-07 14:36:17.6222691 | |
| quarter | quarter | SELECT DATEADD(quarter, 1, @date) | 2022-04-07 14:36:17.6222691 |
| second | s | SELECT DATEADD(s, 1, @date) | 2022-01-07 14:36:18.6222691 |
| second | second | SELECT DATEADD(second, 1, @date) | 2022-01-07 14:36:18.6222691 |
| second | ss | SELECT DATEADD(ss, 1, @date) | 2022-01-07 14:36:18.6222691 |
| week | week | SELECT DATEADD(week, 1, @date) | 2022-01-14 14:36:17.6222691 |
| week | wk | SELECT DATEADD(wk, 1, @date) | 2022-01-14 14:36:17.6222691 |
| week | ww | SELECT DATEADD(ww, 1, @date) | 2022-01-14 14:36:17.6222691 |
| weekday | dw | SELECT DATEADD(dw, 1, @date) | 2022-01-08 14:36:17.6222691 |
| weekday | w | SELECT DATEADD(w, 1, @date) | 2022-01-08 14:36:17.6222691 |
| weekday | weekday | SELECT DATEADD(weekday, 1, @date) | 2022-01-08 14:36:17.6222691 |
| year | year | SELECT DATEADD(year, 1, @date) | 2023-01-07 14:36:17.6222691 |
| year | yy | SELECT DATEADD(yy, 1, @date) | 2023-01-07 14:36:17.6222691 |
| year | yyyy | SELECT DATEADD(yyyy, 1, @date) | 2023-01-07 14:36:17.6222691 |
Note: the nanosecond options did not change the date since the date precision to the right of the decimal is not long enough and we are only adding 1 nanosecond in the examples.
Error if DATEPART Not Supported for Date Format
The following example will display an error message.
select DATEADD(microsecond, 1, CURRENT_TIMESTAMP) as microsecond
The error message is:
The datepart microsecond is not supported by date function dateadd for data type datetime.
This is because the function CURRENT_TIMESTAMP is a datetime data type which does not include microseconds and therefore it is not possible to add microseconds, the same will occur with nanoseconds.
Using DATEPART with Table Data
The following example will show how to add 10 years to the HireDate column in the table.
SELECT HireDate, DATEADD(yy, 10, HireDate) as NewDate FROM HumanResources.Employee
Related Articles
- How to Get Current Date in SQL Server
- Mimic timestamp behavior of other database platforms to store last modified date
- SQL Convert Date to YYYYMMDD
