Stay organized with collections
Save and categorize content based on your preferences.
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 )
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.
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)
[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-07-22 UTC."],[],[],null,["DATETIME_DIFF\n=============\n\nReturns the number of part boundaries between two dates.\n\nSample usage\n------------\n\n`DATETIME_DIFF(Order Date, Ship Date, DAY)`\n\nSyntax\n------\n\n`DATETIME_DIFF( `\u003cvar translate=\"no\"\u003edate_expression\u003c/var\u003e`, `\u003cvar translate=\"no\"\u003edate_expression\u003c/var\u003e`, `\u003cvar translate=\"no\"\u003epart\u003c/var\u003e` )`\n\n### Parameters\n\n- \u003cvar translate=\"no\"\u003edate_expression\u003c/var\u003e - 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.\n- \u003cvar translate=\"no\"\u003epart\u003c/var\u003e - the time part to return. `DATETIME_DIFF` supports the following parts:\n - `MICROSECOND`: available for Date \\& Time fields or expressions.\n - `MILLISECOND`: available for Date \\& Time fields or expressions.\n - `SECOND`: available for Date \\& Time fields or expressions.\n - `MINUTE`: available for Date \\& Time fields or expressions.\n - `HOUR`: available for Date \\& Time fields or expressions.\n - `DAY`\n - `WEEK`: This date part begins on Sunday.\n - `ISOWEEK`: Uses [ISO 8601 week](https://en.wikipedia.org/wiki/ISO_week_date) boundaries. ISO weeks begin on Monday.\n - `MONTH`\n - `QUARTER`\n - `YEAR`\n - `ISOYEAR`: Uses the [ISO 8601](https://en.wikipedia.org/wiki/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.\n\nReturn data type\n----------------\n\nNumber (integer)\n\nExamples\n--------\n\nThe 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.\n\nThe 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.\n\nThe 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.\n\nNotes\n-----\n\nThis function is not available for compatibility mode date types.\n\nRelated resources\n-----------------\n\n- [Dates and times](/looker/docs/studio/dates-and-times)\n- [Calculated fields](/looker/docs/studio/about-calculated-fields)\n- [Looker Studio function list](/looker/docs/studio/function-list)"]]
