DiffDays
Summary
DiffDays computes the difference between two dates or date times.
Syntax
DiffDays(<instant|date_time|date|offset_date_time|zoned_date_time>,<instant|date_time|date|offset_date_time|zoned_date_time>) |
Return Type
Long
Details
The order of the given input parameters impacts the resulting return value. The first input parameter is subtracted from the second input parameter. The return value is number of whole days between the dates or date times. Warning: Be mindful when computing the difference between date times, as the computation returns whole numbers based on number of days.
Examples
/* The order of the input parameters alters the value returned. Refer to the output of the following two examples. */
select DiffDays(ToDate(‘2016-04-01’),ToDate(‘2016-04-15’))
from Empty();
select DiffDays(ToDate(‘2016-04-15’),ToDate(‘2016-04-01’))
from Empty();
/* The following three examples use date time instead of dates. Note how two thousandths of a millisecond changes the calculation result in the last two examples. */
select DiffDays(ToDateTime(‘2016-06-27T23:59:59.999’),ToDateTime(‘2016-06-28T00:00:00.000’))
from Empty();
select DiffDays(ToDateTime(‘2016-06-27T23:23:18.116’),ToDateTime(‘2016-06-29T00:00:00.000’))
from Empty();
select DiffDays(ToDateTime(‘2016-06-27T23:59:59.999’),ToDateTime(‘2016-06-28T23:59:59.998’))
from Empty();
