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.

Syntax

DATE_DIFF(date_expression_a, date_expression_b, date_part)

Where date_part can be any of the following:

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

Practical Info

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:

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)

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

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