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.

SQL Code:

USE mydatabase;

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

SQL Results:

+1 Year

2009-06-31 00:00:00.000

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.

SQL Code:

USE mydatabase;

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

SQL Results:

-1 Day

2006-05-31 00:00:00.000

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.

SQL Code:

USE mydatabase;

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

SQL Results:

-30 Days

2008-07-17 00:00:00.000

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.

SQL Code:

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:

id	customer	day_of_order	product	quantity
1	Tia	2008-08-01 00:00:00.000	Hanging Files	11
2	Tia	2008-08-01 00:00:00.000	Stapler	3
3	A+Maintenance	2008-08-16 00:00:00.000	Hanging Files	14
4	Gerald Garner	2008-08-15 00:00:00.000	19" LCD Screen	5
5	Tia	2008-07-25 00:00:00.000	19" LCD Screen	5
6	Tia	2008-07-25 00:00:00.000	HP Printer	4

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.

Posted in: SQL