May 20, 2021

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.

The DATEADD function is used to add years, months, weeks, or days between to a date.

The general form for the DATEADD is

DATEADD(date part, number, value)

Where date part corresponds to a list of predefined date parts such as year, month, weeks, and days and number specifies the number of date parts to add to value.

If number is positive then that number of date parts are added to the value.

If number is negative, then effective the date parts are subtracted from value.

This function may seem a bit complicated, but it is really worth understanding as it has many practical uses in SQL.

You can use it to calculate the number of days until bill is past due, or the number of days or weeks into the future or past.

Consider this example:

The sales manager has been talking to the production manager about selling items on the web.  They want to produce the bikes as they are ordered.  If bikes were ordered today, when could be they be available to ship to the customer?

If you look at the Product table you’ll see there is a DaysToManufacture field.  Using this with DATEADD in conjunction with GETDATE() we can calculate the number of days into the future.

SELECT NAME,
       DaysToManufacture,
       GETDATE() as Today,
       DATEADD(day, DaysToManufacture, GETDATE()) EarliestDate
FROM   Production.Product
WHERE  DaysToManufacture > 1

The results for this are

Results using DATEADD
Use DATEADD to add DAYS to a DATE

 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

About the author 

Kris Wenzel

Kris Wenzel has been working with databases over the past 30 years as a developer, analyst, and DBA. He has a BSE in Computer Engineering from the University of Michigan and a MBA from the University of Notre Dame. Kris has written hundreds of blog articles and many online courses. He loves helping others learn SQL.

{"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*
>