DateDiff
Returns the time interval between two dates.
Syntax
DateDiff(Val1, Val2, Interval)
Parameters
Val1
Type: Date
First date value.
Val2
Type: Date
Second date value.
Interval
Type: Text
Type of interval in which the difference between the two dates is to be reported. See
Values for Interval.
(return)
Type: Long
Number of intervals by which Val2 is later than Val1.
Remarks
The return value is negative if Val2 is earlier than Val1. If the current data value is NULL, Val1 is 30 December 1899, 00:00:00.
For the calendar intervals, the return value is the number of occurrences of the type of interval from Val1 up to and including Val2, but not includingVal1. For example, from 31 December 2001 to 1 January 2002 is counted as one year, even though the dates are only one day apart. For the “w” interval, the return value is the number of occurrences of whichever day of the week Val1 is, from Val1 up to and includingVal2, but not including Val1.
Example
This example shows the use of the DateDiff function in an SQL query that returns the average time taken, in seconds, to complete a survey:
SELECT
AVG(
DateDiff(DataCollection.StartTime,
DataCollection.FinishTime,
's')
) AS Average
FROM vdata
WHERE DataCollection.Status = {completed}
If this query is run against the UNICOM Intelligence Data File version of the Museum sample data set, the result set is:
Average
-------
2171400
Because the DateDiff function only considers the year part of a date, you have to perform additional manipulation in order to calculate the age of a respondent from the date of birth.
For example, you can use one of the following code examples to calculate the age of a respondent, where DOB is a date field that contains the date of birth of the respondent.
▪DateDiff(DOB, DateNow(), 'yyyy') - (DatePart(DOB, 'y') > DatePart(DateNow(), 'y'))
▪Int(CDouble((Now() - DOB)) / 365.25)
See also