How to Use BETWEEN in BigQuery
Last updated August 23, 2026 · By the SaturnSQL team
BETWEEN is inclusive at both ends. For timestamps use >= start AND < end instead, so you do not lose rows after midnight on the final day.
BETWEEN low AND high is shorthand for value >= low AND value <= high, and the low bound must be the smaller of the two or the range matches nothing. It works on numbers, strings and dates alike.
SELECT * FROM orders
WHERE amount BETWEEN 100 AND 500;The timestamp trap
A DATE range is safe because both ends are whole days. A TIMESTAMP range is not: BETWEEN '2026-08-01' AND '2026-08-31' stops at midnight on the 31st and silently drops almost that entire day. A half-open range avoids the off-by-one and keeps working if the column later gains sub-second precision.
-- Drops most of 31 August
SELECT * FROM events
WHERE created_at BETWEEN '2026-08-01' AND '2026-08-31';
-- Correct
SELECT * FROM events
WHERE created_at >= '2026-08-01'
AND created_at < '2026-09-01';NULLs and negation
A NULL on either side makes the predicate NULL, so the row is not returned by BETWEEN or by NOT BETWEEN. If NULL should count as in range, test for it explicitly.
SELECT * FROM orders
WHERE amount NOT BETWEEN 100 AND 500
OR amount IS NULL;Keep partition pruning
BETWEEN on a partitioning column prunes partitions normally, but only when both bounds are constants or query parameters. Wrapping the column in a function, as in DATE(created_at) BETWEEN ..., defeats pruning and scans the whole table.
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