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. If you are deciding which type a column should be in the first place, we go deeper in BigQuery TIMESTAMP vs DATETIME.

Is BigQuery BETWEEN inclusive for dates?

Yes. BETWEEN is inclusive at both ends, so WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31' keeps rows on both the 1st and the 31st. That is true for a DATE column and it is the answer most people are looking for. The trap is that it stops being the whole truth the moment the column is a TIMESTAMP or a DATETIME.

-- DATE column: inclusive, both ends kept, works as expected
WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31'

-- TIMESTAMP column: silently drops almost all of 31 January
WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31'

The second filter is inclusive of the literal 2026-01-31, but that literal is coerced to 2026-01-31 00:00:00, which is midnight at the very start of the day. Anything that happened at 00:00:01 or later on the 31st is after the upper bound and is discarded. You lose 24 hours of data minus one second, the query succeeds, and the total just looks slightly low.

How do you select data between two dates in BigQuery?

For a timestamp column, use a half-open range instead of BETWEEN: WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01'. The lower bound is inclusive, the upper bound is exclusive, and every instant on 31 January is captured without you having to reason about midnight at all.

-- the pattern to default to on timestamps
SELECT COUNT(*) AS orders
FROM shop.orders
WHERE created_at >= '2026-01-01'
  AND created_at <  '2026-02-01'

-- relative windows, no hardcoded dates
WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)

-- a whole month without typing either boundary
WHERE DATE_TRUNC(order_date, MONTH) = DATE '2026-01-01'

Half-open ranges also chain without gaps or overlaps, which matters when you build one query per month and union them: the exclusive upper bound of January is exactly the inclusive lower bound of February, so no row is counted twice and none falls between. Two further notes. Filter the partition column directly rather than wrapping it in a function, or BigQuery cannot prune partitions and you pay for a full scan. And if the column is a TIMESTAMP but your reporting day is a US calendar day, compare against TIMESTAMP('2026-01-01', 'America/New_York'), because a bare literal is read as UTC and your day boundary will sit five hours off.

Why does DATE_DIFF return a number you did not expect?

Because DATE_DIFF counts the number of date part boundaries crossed between the two dates, not the amount of elapsed time. Google's reference is explicit about this, and its own example is the clearest demonstration: DATE_DIFF(DATE '2017-10-15', DATE '2017-10-14', WEEK) returns 1. Those are consecutive days, but weeks begin on Sunday and the 15th was a Sunday, so exactly one week boundary was crossed.

-- one day apart, but "one week" apart by boundary counting
SELECT
  DATE_DIFF(DATE '2017-10-15', DATE '2017-10-14', DAY)   AS days,   -- 1
  DATE_DIFF(DATE '2017-10-15', DATE '2017-10-14', WEEK)  AS weeks;  -- 1

-- the same pair of dates, two different "year" answers
SELECT
  DATE_DIFF('2017-12-30', '2014-12-30', YEAR)     AS year_diff,     -- 3
  DATE_DIFF('2017-12-30', '2014-12-30', ISOYEAR)  AS isoyear_diff;  -- 2

The YEAR against ISOYEAR pair catches people out for the same reason. YEAR counts Gregorian calendar year boundaries and returns 3, while ISOYEAR returns 2 because 2014-12-30 already belongs to ISO year 2015, whose first Thursday was 2015-01-01 so the ISO year starts on Monday 2014-12-29. If you want whole elapsed weeks or years rather than boundaries crossed, compute in days and divide, or use DATE_DIFF(..., MONTH) with a correction for the day of month. Also remember the argument order is later date first: DATE_DIFF(end, start, part), and reversing it gives you a negative number rather than an error.

How do you get the year or day of week from a date in BigQuery?

Use EXTRACT(part FROM date_expression). EXTRACT(YEAR FROM order_date) gives the year as an INT64, and EXTRACT(DAYOFWEEK FROM order_date) gives the weekday. The detail that causes wrong dashboards is the numbering: Google documents DAYOFWEEK as returning values in the range 1 to 7 with Sunday as the first day of the week, so Sunday is 1 and Saturday is 7.

SELECT
  EXTRACT(YEAR       FROM order_date) AS yr,          -- 2026
  EXTRACT(MONTH      FROM order_date) AS mo,          -- 1 to 12
  EXTRACT(DAYOFWEEK  FROM order_date) AS dow,         -- 1 = Sunday, 7 = Saturday
  FORMAT_DATE('%A',       order_date) AS day_name,    -- 'Monday'
  EXTRACT(WEEK       FROM order_date) AS wk           -- 0 to 53
FROM shop.orders

Two things follow from that numbering. A weekend filter is EXTRACT(DAYOFWEEK FROM order_date) IN (1, 7), not IN (6, 7), which is the single most common off-by-one in BigQuery date logic. And EXTRACT(WEEK) can return 0, because weeks begin on Sunday and any date before the first Sunday of the year sits in week 0, which quietly produces an extra bucket at the start of every January chart. If you need weeks starting on Monday, pass the weekday explicitly with EXTRACT(WEEK(MONDAY) FROM order_date). When you want a readable label rather than a number, FORMAT_DATE('%A', d) is clearer than mapping integers by hand, though it sorts alphabetically, so keep the integer for ORDER BY and show the name.

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.