Use SQL DATEADD to add days, weeks, months, or any timespan specified by datepart to a date to get another. The function returns the modified date.

Description

The function adds or deletes a specified time period from the date value. The period value can be a year, month, day, week, hour, minute, second, etc. A negative integer argument gives a date value in the past.

 SQL DATEADD Usage Notes

  • The first argument in the SQL DATEADD function is the date part to be added or subtracted from the given date value.
  • Integer as the second argument is a positive or a negative number. It specifies the number of times to add or subtract the date part from the input date value.
  • In the case of a decimal value, the DATEADD function does not round the number. It simply truncates the decimal part.
  • The date value is the input date to be modified by the SQL DATEADD function. This argument can be a date, datetime, datetimeoffset, datetime2, smalldatetime, or a time data type.
  • The date value argument should resolve to the above-mentioned data types in case of a string, column value, or a user-defined variable.
  • The return type of the function is datetime if the input value is a string. In the case of any other data type, the function returns the same data type.

Syntax

DATEADD ( date_part, integer, date_value)

SQL DATEADD Examples

Following is a simple demonstration of adding a time interval using the DATETIME function.

SELECT DATEADD(MONTH, 1, '2018-12-31') newDate;
/* Answer */
SELECT DATEADD(MONTH, 1, '2018-12-31') newDate;

The above query adds a month to the given date value. The output value is ‘2019-01-31 00:00:00.000’.

If we use a negative integer, we get a date value in the past.

SELECT DATEADD(DAY, -2, '2018-12-31') pastDate;
/* Answer */
SELECT DATEADD(DAY, -2, '2018-12-31') pastDate;

We get an output value of ‘2018-12-29 00:00:00.000’.

We will use the Adventureworks2019 database for the following examples.

The following query runs on BillsofMaterials record. It adds 20 days to the StartDate column values to calculate the EndDate of using each component.

SELECT BillOfMaterialsID, ComponentID, StartDate, DATEADD(day, 20, StartDate) estimatedEndDate FROM Production.BillOfMaterials WHERE EndDate IS NULL ORDER BY estimatedEndDate DESC;
/* Answer */
SELECT BillOfMaterialsID, ComponentID, StartDate,
       DATEADD(day, 20, StartDate) estimatedEndDate
FROM   Production.BillOfMaterials
WHERE  EndDate IS NULL
ORDER BY estimatedEndDate DESC;

As for the EndDate column, NULL values are allowed. We can calculate the EndDate of such NULL records as shown above. The Where clause checks if the EndDate is NULL. The order by clause orders the result in decreasing order of calculated end dates.

In the following example, we will see how to use the SQL DATEADD function with user-defined variables. We calculate the expiration date using the DATEADD function.

DECLARE @usagedays INT = 120, @manufacturedate DATETIME = '2021-05-01'; SELECT DATEADD(day, @usagedays, @manufacturedate) expirationDate;
/* Answer */
DECLARE @usagedays INT = 120,   
        @manufacturedate DATETIME = '2021-05-01';   
SELECT DATEADD(day, @usagedays, @manufacturedate) expirationDate;  

Usagedays and manufacturedate are user defined variables. We added usage days equal to four months to get the expiration date.

See Also

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Are YOU READY TO START LEARNING SQL SERVER, BUT DON'T KNOW WHERE TO START?

Nothing is worse than, being excited to learn a new tool but not knowing where to start, wasting time learning the wrong features, and being overwhelmed .

But it doesn't have to be this way.

I'm Putting together a free email course to help you get started learning SQL Server.

Name*
Email*
>