SQL – DateAdd()

DATEADD() is the SQL function used to add and increment date values. Hours, minutes, months, and days can be added to any date value. In fact, dates can be added based on any type of date part discussed in the SQL DATEPART() lesson.

USE mydatabase;

SELECT DATEADD(year, 1, getdate()) AS "+1 Year";

SQL Results:

This example shows how to use DATEADD() to take a specified date value and increment it by the ‘year’ date part. By replacing the middle parameter with a negative value, we can utilize the same DATEADD() function to subtract dates as well.

USE mydatabase;

SELECT DATEADD(day,-1, '2006-06-01') AS "-1 Day";

SQL Results:

In each example, SQL is able to perform a calculation on each date value based on a timestamp, and after the calculation, a timestamp value returned. Also note that the date parameter can be based on another SQL function or the result of a subquery.

USE mydatabase;

SELECT DATEADD(day,-30, (SELECT MAX(day_of_order) FROM orders)) AS "-30 Days";

SQL Results:

Here we have now constructed a very useful, dynamic statement pulling the most current order (MAX) in the orders table, and we’ve been able to subtract one day from that value. While this information does not directly prove useful, if we take this query one step further and place this statement in a WHERE as a subquery, we should be more satisfied with the results.

USE mydatabase;

SELECT * 
FROM orders
WHERE day_of_order >
(SELECT DATEADD(day,-30, (SELECT MAX(day_of_order) FROM orders)) AS "-30 Days");

SQL Results:

By placing this calculated date in the WHERE clause, we were able to pull all the records that have happened within 30 days of the most recent order (2008-07-17 00:00:00.000). We are able to query the orders table and request this information with a dynamic query that will yield different results as new orders are placed and time goes by.