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
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.
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.
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.
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.
Usagedays and manufacturedate are user defined variables. We added usage days equal to four months to get the expiration date.
