# 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](https://en.wikipedia.org/wiki/ISO_week_date) boundaries. ISO weeks begin on Monday.
  - `MONTH`
  - `QUARTER`
  - `YEAR`
  - `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.

## 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.

## Related resources

- [Dates and times](https://docs.cloud.google.com/data-studio/dates-and-times)
- [Calculated fields](https://docs.cloud.google.com/data-studio/about-calculated-fields)
- [Data Studio function list](https://docs.cloud.google.com/data-studio/function-list)