SQL Server DATEADD() Function

In SQL Server, the DATEADD() function adds a number to a datepart and returns the modified datetime value.

Syntax:

DATEADD(datepart, number, date)

Parameters

datepart: The specific part of the specified date parameter to which the DATEADD() function will add a number. The following table lists all valid datepart values:

Note: dayofyear, day, and weekday return the same value.

number: An integer that is to be added to the specified datepart. It can be an expression that returns an integer. The number will be truncated if it has a decimal fraction.

Note: DATEADD() function returns an error if the number argument exceeds the range of int.

date: A datetime value or an expression that resolves to a datetime value. It can be a string expression, a table column, or a variable. A string literal value must resolve to type DATE, DATETIME, DATETIMEOFFSET, DATETIME2, SMALLATETIME, or TIME

Return Value

The return type depends on the argument supplied for the date parameter.

If the value of the date is a string literal date value, then DATEADD() returns a datetime value. If any other valid data type is supplied for the date column, then the same data type is returned from DATEADD() function.

Add Days in DateTime

The following example adds 1 day to the specified date '12/31/2021' (31st December 2021).

SELECT DATEADD(day,1,'12/31/2021') AS Result

In the above example, 'day' is the datepart that specifies that a number should be added to a day part of the specified date '12/31/2021'. The second parameter is a number that should be added, and the third parameter is a date where the specified parameter should be added.

Add Months in DateTime

In this example, for the month datepart, 2 is added to date '12/31/2021'. As you can see in the result, two months is added to the input date and the DATEADD() function returns 28th February 2022, which is the last day of the returned date.

SELECT DATEADD(month, 2, '12/31/2021') AS Result

You can specify negative number to be added in the date which will deduct a number from the specified datetime value.

SELECT DATEADD(month, -2, '12/31/2021') AS Result

Use DATEADD() with Columns

The following DATEADD() function is used with the HireDate column of the Employee table to return the JoinDate. Two weeks is added to the HireDate column to return JoinDate.

SELECT EmployeeID, FirstName, HireDate, DATEADD(wk, 2, HireDate) AS JoinDate 
FROM Employee

Make sure the specified date is in the correct format; otherwise it will raise an error "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."

SELECT DATEADD(month, -2, '12/31/2021 123') AS Result