SQL Server DATEADD Function


By:

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
simple example

Using DATEADD with NULL Values

If a NULL value is used, the result will be NULL.

SELECT DATEADD(s, 1, NULL) as dateadd
dateadd with null values

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 qq 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
dateadd with tables

Related Articles


Last Update: 1/7/2022




Comments For This Article





download














get free sql tips
agree to terms