DATE_DIFF
See also DATETIME_DIFF, TIMESTAMP_DIFF, TIME_DIFF
Definition
The DATE_DIFF
function allows you to find the difference between 2 date objects in the specified date_part
interval.
Where date_part
can be any of the following:
DATE_DIFF(date_expression_a, date_expression_b, date_part)
WEEK
: Begins on SundayWEEK()
: Begins on where WEEKDAY can be SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, and SATURDAY.ISOWEEK
: Uses ISO 8601 week boundaries. ISO weeks begin on Monday.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.
days_difference | weeks_difference |
---|---|
366 | -5 |
Practical Info
- If the first date is earlier than the second one then the output will be negative.
Common Questions
How to filter for the last n days?
A common SQL query WHERE clause is to just pull the data for the last n days. In this case, we can make use of DATE_DIFF
and CURRENT_DATE
:
date | days_since |
---|---|
2021-01-22 | 21 |
2021-01-26 | 17 |
Troubleshooting Common Errors
Argument 2 of DATE_DIFF has incorrect type: expected date found datetime.
This one is all too common. If you're using DATE_DIFF
, you'll need to make sure both of your dates are indeed DATE
data types, and not DATETIME
or TIMESTAMPs
. It's usually easy enough to add a CAST(datetime_col as DATE)
to your function:
DATE_DIFF does not support the SECOND date part
Similar to the first common error, this about the compatibility of the argument data types and the function. To find the difference between times, you'll need to use DATETIME_DIFF
, TIMESTAMP_DIFF
, or TIME_DIFF
.
Related Pages
- DATETIME_DIFF
- TIMESTAMP_DIFF
- Dates & Times in BigQuery