DATE_DIFF

The DATE_DIFF function allows you to find the difference between 2 date objects in the specified date_part interval.

DATE_DIFF(date_expression_a, date_expression_b, date_part)

Where date_part can be any of the following:

• DAY

• WEEK : Begins on Sunday

• WEEK(<WEEKDAY>): Begins on <WEEKDAY> where WEEKDAY can be SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, and SATURDAY.

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

SELECT
  DATE_DIFF(CAST('2021-01-01' AS DATE), CAST('2020-01-01' AS DATE), DAY) AS days_difference,
  DATE_DIFF(CAST('2021-01-01' AS DATE), CAST('2021-02-01' AS DATE), WEEK) AS weeks_difference

• If the first date is earlier than the second one then the output will be negative.

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:

SELECT
  date,
  DATE_DIFF(CURRENT_DATE(), date, DAY) AS days_since
FROM
  (
    SELECT
      CAST('2021-01-22' AS DATE) AS date
    UNION ALL
(    SELECT
      CAST('2020-01-25' AS DATE) AS date)
    UNION ALL
(    SELECT
      CAST('2021-01-26' AS DATE) AS date)
  ) AS table_3
WHERE
  (DATE_DIFF(CURRENT_DATE(), date, DAY) <= 30)

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(CAST('2020-01-01 10:44:22' as DATE),'2021-01-05', DAY) days_diff

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.

• DATETIME_DIFF

• TIMESTAMP_DIFF

• Dates & Times in BigQuery