DateAdd(interval, number, date)
For adding and subtracting dates and times, the easiest function to use is the DateAdd() function. Using the DateAdd() function requires passing a string representing the type of interval to modify, the number of units to add or subtract, and the date to modify. There are a number of different strings that designate the interval to modify. These interval strings are listed in Table 6-13. There is no such thing as a DateSubtract() function. To subtract a date interval, pass a negative number of units. The DateAdd() function returns a DateTime value and this may need to be converted to either a Date or a Time depending on how you intend to use the result.
The q interval is for adding financial quarters. Using one q interval unit is the same as using three m intervals (for months). The benefit of using the q interval is that many financial reports are printed on a quarterly basis. After the user is prompted for how many quarters they wish to print, you can take their input and use it to calculate a final date. Although you could use the month interval and multiply their input by 3, having a shortcut is nice and it helps make your code self-documenting.
Rather than using the DateAdd() function to add and subtract days, it is just as acceptable to directly add a number to the Date variable. Since the date is stored as a number, adding another number to it increases the date by that number of days. The following examples both produce the same result.
The benefit to using the DateAdd() function is that it takes into account how many days are in each month and it checks for valid dates. As an example, say that you want to find out the last day of the next month. To do this with the addition operator, you need to know how many days are in the next month. You also need to track which years are leap years. Using the DateAdd() function is much easier because if you add one month to the current date, it will check that this returns a valid date. If there aren’t enough days in the month then it will return the last valid day of the month. The same applies to using the quarter interval. The function adds three months to the current date and makes sure that this is a valid date. If not, it returns the last valid date of the quarter.
DateDiff(interval, startdate, enddate, firstdayofweek)
The DateDiff() function returns the difference between two dates or times. It can return the difference in different intervals. These intervals can be days, months, years or any of the intervals listed in Table 6-13.
Be careful when using the DateDiff() function for calculating an interval other than the number of days. When performing a difference calculation, it counts any interval less than a single unit as zero. For example, if you want to find out how many months have elapsed between two dates, and the two dates are the Jan 1 and Jan 31, then the result is 0. This is because the interval is only a partial month and doesn’t constitute a full month. The fact that the dates are 30 days apart is irrelevant. If you change this example so that rather than use the last day of the month, you use the first day of the next month, then the result is 1. Even though the two examples had final dates that only differed by one day, the result is different. This applies to all the intervals including dates and times.
There is an optional argument that lets you specify the first day of the week. This is only used by the DateDiff() function when the interval is ww. This counts the number of times a particular day of the week appears within a date range. To pass this argument to the function, prefix the day by cr For example, Friday is crFriday. The start date does not get counted when doing the calculation, but the end date does. Thus, if you pass the function a start date that falls on a Friday, and the argument is crFriday, then the result will not include this date.
The DateDiff() function treats the w and ww intervals differently than the DateAdd() and DatePart() functions. In both the DateAdd() and DatePart() functions, the w interval represents a single weekday and the ww interval represents a seven day period. However, the DateDiff() function treats the w interval as the number of weeks between two dates and the ww interval counts the number of times a certain weekday appears. Thus, the ww interval counts the number of times a seven-day period occurs and the w interval counts the number of times a single day occurs. These two functions treat the intervals in opposite ways.
