| Lesson 13 | The DATEADD function |
| Objective | Understand how the DATEADD function is used. |
SQL DateAdd Function
There are many cases in which you will want to add a specified number of days to a date and get the result. For example, if you are writing a report that will show when account receivables will come due, you could query the sales date and add 30 days to determine when the bill would be due.
There are some engines that require a shorthand version of these intervals. The shorthand version is shown in the parentheses next to the full words above. You can use either the full word or the shorthand, but not both. Also, do not include the parentheses.
DATEADD does just this.
You tell it the known date and the number increments to add. The common values you can use to indicate this include
year (yy), quarter (qq), month (mm), day (dd), week (wk), hour (hh), minute (mi), and second (ss).There are others, but they are likely less useful in mainstream queries.
There are some engines that require a shorthand version of these intervals. The shorthand version is shown in the parentheses next to the full words above. You can use either the full word or the shorthand, but not both. Also, do not include the parentheses.
The syntax is:
SELECT DateAdd(interval, count, 'date')
So, to add a week to a given date, you can use the following statement:
SELECT 'New Date' = DateAdd(week, 1, 'May 2, 1999')
This returns a new column with a value of May 9, 1999. Remember that if you are providing the date in your statement (rather than referring to a column name), you need to enclose it in single or double quotes. In addition, you may have to include any other identifiers required by your engine.
Listing 5-13.1 Increment today's date to six months hence, in one-month intervals.
See Figure 4-13.1 for the result.
SELECT
i AS MonthsAhead,
DATEADD("m", i, CURRENT_TIMESTAMP)
AS FutureDate
FROM seq
WHERE i BETWEEN 1 AND 6;
Result of Listing 4-13.1