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.
SELECT DATEADD(year, 1, getdate()) AS "+1 Year";
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.
SELECT DATEADD(day,-1, '2006-06-01') AS "-1 Day";
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.
SELECT DATEADD(day,-30, (SELECT MAX(day_of_order) FROM orders)) AS "-30 Days";
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.
WHERE day_of_order >
(SELECT DATEADD(day,-30, (SELECT MAX(day_of_order) FROM orders)) AS "-30 Days");
|1||Tizag||2008-08-01 00:00:00.000||Hanging Files||11|
|3||A+Maintenance||2008-08-16 00:00:00.000||Hanging Files
|4||Gerald Garner||2008-08-15 00:00:00.000||19" LCD Screen||5|
|5||Tizag||2008-07-25 00:00:00.000||19" LCD Screen||5|
|6||Tizag||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.
Found Something Wrong in this Lesson?
Report a Bug or Comment on This Lesson - Your input is what keeps Tizag improving with time!