SQL DATEADD Function Use and Examples

Overview

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.

Explanation

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'

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

Additional Information

• 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