Calculate Differences Between Dates
DATEDIFF returns the total number of the specified unit between startDate and endDate. For example, this statement calculates the number of minutes between dates. It evaluates both the date and time components. For each day difference, it adds 1440 minutes, which is the number of minutes in a day.
SELECT DATEDIFF('mi','1910-08-21 08:32:04','1910-08-28 01:45:00')
DATEDIFF does not account for the actual duration between dates. In this way, it can be considered as a count of the specified date part boundaries crossed between startDate and endDate. For example, these differences between consecutive years all return a DATEDIFF of 1, even though their durations are greater than or less than 365 days.
SELECT DATEDIFF('yyyy','1910-08-21','1911-08-21') AS ExactYear,
DATEDIFF('yyyy','1910-06-30','1911-01-01') AS HalfYear,
DATEDIFF('yyyy','1910-01-01','1911-12-31') AS Nearly2Years,
DATEDIFF('yyyy','1910-12-31 11:59:59','1911-01-01 00:00:00') AS NewYearSecond
Similarly, in this statement, the difference these consecutive minutes is 1, even though only 6 seconds actually separate the two values.
SELECT DATEDIFF('mi','12:23:59','12:24:05') AS MinuteDiff
The previous statements used abbreviations for the date part. Alternatively, you can specify the full name of the date part. For example:
SELECT DATEDIFF('minute','12:23:59','12:24:05') AS MinuteDiff
This Embedded SQL example uses host variables to perform the same DATEDIFF operation as the previous statement:
set datePart="minute"
set startDate="12:23:59"
set endDate="12:24:05"
&sql(SELECT DATEDIFF(:datePart,:startDate,:endDate) INTO :diff)
WRITE diff, !
This statement uses DATEDIFF in the WHERE clause to select patients admitted in the last week:
SELECT Name,DateOfAdmission FROM Sample.Patients WHERE DATEDIFF(D,DateOfAdmission,$HOROLOG) <= 7
This statement uses a subquery to return those records where the person’s date of birth is 1500 days or fewer from the current date:
SELECT Name,Age,DOB
FROM (SELECT Name,Age,DOB, DATEDIFF('dy',DOB,$HOROLOG) AS DaysTo FROM Sample.Person)
WHERE DaysTo <= 1500
ORDER BY Age