DATETIME_DIFF

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.

Formula	Result
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.

Formula	Result
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.

Formula	Result
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.

Related resources

• Dates and times
• Calculated fields
• Looker Studio function list