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_DIFF supports 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 below 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.