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