BigQuery Date Functions: Format, Add, Subtract, and Group by Date

BigQuery date functions let you format a date, do arithmetic on it, and group rows by day, week or month. The ones you reach for daily are CURRENT_DATE(), FORMAT_DATE(), DATE_ADD() and DATE_SUB(), DATE_DIFF(), DATE_TRUNC() and EXTRACT(). This is a working reference for each, with the exact syntax and the mistakes that cost people an afternoon, plus how the DATE, DATETIME and TIMESTAMP types differ so you call the right function family.

The functions you will use most

Formatting a date as a string

Use FORMAT_DATE() to turn a DATE into text in whatever layout you need. The format elements follow the standard strftime codes.

SELECT
  FORMAT_DATE('%Y-%m-%d', order_date) AS iso,      -- 2026-07-21
  FORMAT_DATE('%Y%m%d', order_date)   AS yyyymmdd, -- 20260721
  FORMAT_DATE('%Y-%m', order_date)    AS year_month -- 2026-07
FROM sales.orders

The %Y%m%d form (no separators) is the one people search for most, usually because a downstream system or filename wants a compact 20260721. Remember the result is a STRING, so do not try to do date math on it afterwards. Format at the very end, once the arithmetic is done.

Adding and subtracting time

DATE_ADD and DATE_SUB take an INTERVAL and a unit (DAY, WEEK, MONTH, QUARTER, YEAR). They are the safe way to shift a date, because they handle month lengths and leap years for you.

-- orders in the last 30 days
SELECT order_id
FROM sales.orders
WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)

Do not build date windows by hand with string math or by adding raw numbers to a date. DATE_ADD(DATE '2026-01-31', INTERVAL 1 MONTH) correctly returns 2026-02-28, which no naive arithmetic gets right.

Measuring the gap between two dates

DATE_DIFF(later, earlier, unit) returns the whole-unit difference. The argument order is later date first, and getting it backwards gives you a negative number, which is the single most common DATE_DIFF bug.

SELECT
  customer_id,
  DATE_DIFF(CURRENT_DATE(), signup_date, DAY) AS days_since_signup
FROM customers

Note that DATE_DIFF counts boundary crossings, not elapsed time. DATE_DIFF('2026-01-01', '2025-12-31', DAY) is 1, and by MONTH two dates one day apart across a month boundary return 1 as well. If you need true elapsed days, DAY is what you want; if you need calendar-month counts, expect boundary behavior.

Grouping a trend by day, week, or month

This is the reason date functions exist for most analysts: turning per-row timestamps into a time series. DATE_TRUNC() rounds each date down to the start of its period, so all of January collapses to 2026-01-01 and groups cleanly.

SELECT
  DATE_TRUNC(order_date, MONTH) AS month,
  COUNT(*) AS orders,
  SUM(total) AS revenue
FROM sales.orders
GROUP BY month
ORDER BY month

Truncate rather than FORMAT_DATE for grouping, because DATE_TRUNC keeps the result a real DATE that sorts chronologically. If you group by a '%Y-%m' string instead, December 2025 and January 2026 still sort correctly, but any format that leads with the month sorts wrong. Truncate to group and sort, format only for display at the end.

DATE vs DATETIME vs TIMESTAMP

BigQuery has three date and time types, and the function you call has to match, or you get a type error.

TIMESTAMP is the tricky one because it is zone-aware. TIMESTAMP_TRUNC(ts, DAY) truncates in UTC unless you pass a time zone as a third argument, so TIMESTAMP_TRUNC(ts, DAY, 'America/New_York') is what you want when a "day" should mean a US calendar day rather than a UTC one. Grouping revenue by day without the time zone is a classic cause of numbers that are slightly off near midnight.

Ask it in plain English instead

You do not have to memorize which of the DATE, DATETIME and TIMESTAMP functions to call. Agentsql connects read-only to BigQuery, turns a plain-English question like "monthly revenue for the last year" into the correct Standard SQL, picks the right DATE_TRUNC and time zone, runs it, and shows you the query so you can check it. See the BigQuery integration for how it connects and the SQL query generator for how the translation works, then see how it works and ask BigQuery a question in plain English.