How to Use DATE_DIFF in BigQuery
Last updated July 26, 2026 · By the SaturnSQL team
DATE_DIFF(later_date, earlier_date, unit) returns the number of unit boundaries crossed. Get the argument order backwards and the result comes out negative.
SELECT DATE_DIFF('2026-07-26', '2026-07-01', DAY) AS days_elapsed;
-- 25The argument order gotcha
Unlike some databases, BigQuery wants the later date first: DATE_DIFF(later, earlier, unit). Swap them and you get a negative number instead of an error, which is easy to miss in a dashboard.
SELECT
DATE_DIFF(CURRENT_DATE(), signup_date, DAY) AS correct_days_since_signup,
DATE_DIFF(signup_date, CURRENT_DATE(), DAY) AS wrong_negative_result
FROM users;Other units
unit can be DAY, WEEK, MONTH, QUARTER, or YEAR. DATE_DIFF counts calendar boundaries crossed, not full 30-day months, so Jan 31 to Feb 1 counts as 1 MONTH even though only a day passed.
SELECT DATE_DIFF(CURRENT_DATE(), signup_date, MONTH) AS months_since_signup
FROM users;Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free