The function DATE_DELTA returns returns the number of total years, total months, and total days between two DATES.
This function takes two Dates as input. It returns he number of total years, total months, and total days between the two Dates. This output is technically formatted as another Date, but, unlike most Dates, it does not express a valid date. Calculation takes into account irregularities such as daylight savings time and leap years.
Declaration
DATE_DELTA(date1, date2) -> delta
Parameters
date1 (required, type: Date)
Any date.
date2 (required, type: Date)
Any date.
Return Values
delta (type: Date)
The number of total years, total months, and total days between two date1 and date2. Calculation takes into account irregularities such as daylight savings time and leap years.
Unlike most Dates, delta does not express a valid date.
Examples
Assume the following examples have access to the following Date values:
example_date1 = {
"day": 10
"month": 7
"year": 2018
}
example_date2 = {
"day": 26
"month": 4
"year": 2021
}
The following example returns the the number of total days, total months, and total years between example_date1 and example_date2. Note that the values associated with "day" and "month" are too large to be valid values describing a real Date; rather, the describe the total number of days and months (respectively) between the two given Dates:
DATE_DELTA(example_date1, example_date2) = {
"day": 1021,
"month": 33
"year": 2
}
DATE_DELTA returns the absolute difference between the given Dates; it does not matter which is given first. The following example returns the the number of days, months, and years between example_date2 and example_date1; note that the output is the same as the above example, even though the Date values are given in reverse order:
DATE_DELTA(example_date2, example_date1) = {
"day": 1021,
"month": 33
"year": 2
}
Discussion
The Date returned by DATE_DELTA does not contain conventional Date information; while it is technically possible to use it as Date input in functions such as FORMAT_DATE or ISO_WEEK, the output returned in such cases will not be meaningful. To avoid such confusion, it is best practice to use DATE_DELTA in tandem with the DAY, MONTH, or YEAR function, like so:
DAY(DATE_DELTA(date1, date2)) -> day_delta
MONTH(DATE_DELTA(date1, date2)) -> month_delta
YEAR(DATE_DELTA(date1, date2)) -> year_delta
This returns a single Number: the days, months, or years, respectively, between the two dates.
Assume the following examples have access to example_date1 and example_date2 from the Examples section. The isolated difference between days, months, and years (respectively) between them can be found as follows:
DAY(DATE_DELTA(example_date1, example_date2)) -> 1021
MONTH(DATE_DELTA(example_date1, example_date2)) -> 33
YEAR(DATE_DELTA(example_date1, example_date2)) -> 2