DateDiff
Returns the time interval between two dates.
Syntax
DateDiff(<value1>, <value2>, <interval>)
Parameters
<value1>
Type: Date
First date value.
<value2>
Type: Date
Second date value.
<interval>
Type: Text
Type of interval in which the difference between the two dates is to be reported. See
Value of Interval.
(return)
Type: Long
Number of intervals by which <value2> is later than <value1>.
Notes
The return value is negative if <value2> is earlier than <value1>. If the current data value is NULL, <value1> 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 <value1> up to and including <value1>, but not including <value1>. For example, from 31 December 2020 to 1 January 2021 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 <value1> is, from <value1> up to and including <value2>, but not including <value1>.
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 considers only the year part of a date, you have to perform additional manipulation 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