The DATEADD function performs time and date calculations for matching properties having date types. Use the DATEADD function to obtain dates and times in a specified amount of time before the present. The following example shows the DATEADD function:
DateAdd(interval, number, date)
The DateAdd function syntax has these named arguments:
|Interval||Required. String expression that is the interval of time you want to add.|
|Number||Required. Numeric expression that is the number of intervals you want to add. It can be positive (to get dates in the future) or negative (to get dates in the past).|
|Date||Required. Variant (Date) or literal representing date to which the interval is added.|
The Interval argument has these settings:
|y||Day Of Year|
You can use the DateAdd function to add or subtract a specified time interval from a date. For example, you can use DateAdd to calculate a date 30 days from today or a time 45 minutes from now.
Let's look at the following example:
Suppose we sell electrical products, and with a product we can sell Manufacturers Guarantees. Now we will record the GuaranteeStartDate in the OrderDetails table, and also record the Guarantee Period, as these may run for 1, 2, 3, 4 or 5 Years in length.
Our table will look like this:
Now to view the date when the Guarantee Renewal is due, we can create a query based upon this table, and use the DateAdd function to provide the data we need:
Query design, showing the additional dateadd function column added.
The additional column has been added to the query design, that will display the calculated renewal date - RenewalDate: DateAdd("yyyy",[GuaranteePeriod],[GuaranteeStartDate])
This uses the Interval in Years, the Number taken from the [GuaranteePeriod] field, and the Date from the [GuaranteeStartDate] field.
The resulting data is shown below:
The results of using the DateAdd function to display the Guarantee RenewalDate.