SQL Server DATEADD Function

Summary: in this tutorial, you will learn how to use the SQL Server DATEADD() function to add a specified value to a specified date part of a date.

SQL Server DATEADD() function overview

The DATEADD() function adds a number to a specified date part of an input date and returns the modified value.

The following shows the syntax of the DATEADD() function:

DATEADD (date_part , value , input_date ) 
Code language: SQL (Structured Query Language) (sql)

The DATEADD() function accepts three arguments:

• date_part is the part of date to which the DATEADD() function will add the value. (See the valid date parts in the table below)
• value is an integer number to be added to the date_part of the input_date. If the value evaluates to a decimal or float, the function DATEADD() will truncate the decimal fraction part. It will not round the number in this case.
• input_date is a literal date value or an expression which can resolve to a value of type DATE, DATETIME, DATETIMEOFFSET, DATETIME2, SMALLATETIME, or TIME

The following table lists the valid values of date_part:

The function DATEADD() function returns a new date value after adding the value to the date_part.

SQL Server DATEADD() function examples

Let’s take some examples of using the DATEADD() function.

Add 1 second to 2018-12-31 23:59:59

SELECT 
    DATEADD(second, 1, '2018-12-31 23:59:59') result;
Code language: SQL (Structured Query Language) (sql)

Here is the output

result
-----------------------
2019-01-01 00:00:00.000

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

Adding 1 day to 2018-12-31 00:00:00:

SELECT 
    DATEADD(day, 1, '2018-12-31 23:59:59') result;
Code language: SQL (Structured Query Language) (sql)

The output is:

result
-----------------------
2019-01-01 23:59:59.000

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

This example uses the DATEADD() function to calculate the estimated shipped date based on the ordered date:

SELECT 
    order_id, 
    customer_id, 
    order_date,
    DATEADD(day, 2, order_date) estimated_shipped_date
FROM 
    sales.orders
WHERE 
    shipped_date IS NULL
ORDER BY 
    estimated_shipped_date DESC;
Code language: SQL (Structured Query Language) (sql)

The following picture shows the partial output:

Handling month examples

If you add a number of months to a date and the day of the date result does not exist, the DATEADD() function will return the last day of the return month.

See the following example:

SELECT 
    DATEADD(month, 4, '2019-05-31') AS result;
Code language: SQL (Structured Query Language) (sql)

In this example, the month of the return date is September. However, day 31 does not exist in September, therefore, the DATEADD() function returns the last day of September (30) as the day for the result date:

result
-----------------------
2019-09-30 00:00:00.000

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

Notice that the following query returns the same result:

SELECT 
    DATEADD(month,4,'2019-05-30') AS result;
Code language: SQL (Structured Query Language) (sql)

Output:

result
-----------------------
2019-09-30 00:00:00.000

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the SQL Server DATEADD() function to add a specified value to a date part of a date.