Survey Tabulation > Advanced expressions > UNICOM Intelligence function library > Date and time functions > DateDiff
 
DateDiff
Returns the time interval between two dates.
Syntax
DateDiff(Val1, Val2, Interval)
Parameter
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.
The following table shows the valid values for Interval.
Value of Interval
Description
"yyyy"
Calendar years
"q"
Calendar quarters
"m"
Calendar months
"d"
Day
"y"
Day of year (same as "d")
"w"
Week (seven-day periods)
"ww"
Calendar weeks (using first-day-of-week setting from input locale)
"h"
Hour
"n"
Minute
"s"
Second
(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
See also
Date and time functions