DATETIME_DIFF
Returns the number of part boundaries between two dates.
Sample usage
DATETIME_DIFF(Order Date, Ship Date, DAY)
Syntax
DATETIME_DIFF( date_expression
, date_expression
, part
)
Parameters
-
date_expression- a Date or a Date & Time field or expression. If the first date expression occurs before the second date expression, then the result is zero or negative. -
part- the time part to return.DATETIME_DIFFsupports the following parts:-
MICROSECOND: available for Date & Time fields or expressions. -
MILLISECOND: available for Date & Time fields or expressions. -
SECOND: available for Date & Time fields or expressions. -
MINUTE: available for Date & Time fields or expressions. -
HOUR: available for Date & Time fields or expressions. -
DAY -
WEEK: This date part begins on Sunday. -
ISOWEEK: Uses ISO 8601 week boundaries. ISO weeks begin on Monday. -
MONTH -
QUARTER -
YEAR -
ISOYEAR: Uses the ISO 8601 week-numbering year boundary. The ISO year boundary is the Monday of the first week whose Thursday belongs to the corresponding Gregorian calendar year.
-
Return data type
Number (integer)
Examples
The second and third example in the following table show the result of DATETIME_DIFF
for two date values that are 24 hours apart. DATETIME_DIFF
with the part WEEK
returns 1 because DATETIME_DIFF
counts the number of part boundaries in this range of date values. Each WEEK
begins on Sunday, so there is one part boundary between Saturday, 2017-10-14 00:00:00 and Sunday, 2017-10-15 00:00:00.
| Example formula | Output |
|---|---|
DATETIME_DIFF( DATETIME "2010-07-07 10:20:00", DATETIME "2008-12-25 15:30:00", DAY )
|
559 |
DATETIME_DIFF( DATETIME "2017-10-15 00:00:00", DATETIME "2017-10-14 00:00:00", DAY )
|
1 |
DATETIME_DIFF( DATETIME "2017-10-15 00:00:00", DATETIME "2017-10-14 00:00:00", WEEK )
|
1 |
The following example shows the result of DATETIME_DIFF
for two dates in different years. DATETIME_DIFF
with the date part YEAR
returns 3 because it counts the number of Gregorian calendar year boundaries between the two date values. DATETIME_DIFF
with the date part ISOYEAR
returns 2 because the second date value belongs to the ISO year 2015. The first Thursday of the 2015 calendar year was 2015-01-01, so the ISO year 2015 begins on the preceding Monday, 2014-12-29.
| Example formula | Output |
|---|---|
DATETIME_DIFF(DATETIME '2017-12-30 00:00:00', DATETIME '2014-12-30 00:00:00', YEAR)
|
3 |
DATETIME_DIFF(DATETIME '2017-12-30 00:00:00', DATETIME '2014-12-30 00:00:00', ISOYEAR)
|
2 |
The following example shows the result of DATETIME_DIFF
for two days in succession. The first date falls on a Monday and the second date falls on a Sunday. DATETIME_DIFF
with the date part ISOWEEK
also returns 1 because ISO weeks begin on Monday.
| Example formula | Output |
|---|---|
| DATETIME_DIFF(DATE '2017-12-18', DATE '2017-12-17', WEEK) | 0 |
DATETIME_DIFF(DATE '2017-12-18', DATE'2017-12-17', ISOWEEK)
|
1 |
Notes
This function is not available for compatibility mode date types.
