SQL BETWEEN Is Inclusive: Safer Date Range Queries

By Sharon Ben-Moshe · August 21, 2026 · 2 min read

Sharon Ben-Moshe is the founder of sqlfmt, a browser-based SQL formatter and validator with misspelled-keyword suggestions across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.

SQL BETWEEN includes both endpoints. Learn the timestamp boundary trap and use half-open date ranges that stay correct across databases.

Quick answer

SQL BETWEEN includes both its lower and upper bounds. That is usually fine for whole numbers, but it can miss rows at the end of a timestamp range. For timestamp data, prefer a half-open range: start inclusive and the next boundary exclusive.

FAQs

Is SQL BETWEEN inclusive?

Yes. SQL BETWEEN includes both endpoints. `x BETWEEN 10 AND 20` has the same boundary behavior as `x >= 10 AND x <= 20`.

Why should I avoid BETWEEN for timestamps?

BETWEEN can be correct for timestamps only when the upper endpoint includes the exact final instant wanted. A bare date commonly begins at midnight, so a half-open range is clearer and safer.

Does BETWEEN work with text values?

BETWEEN can compare text values, but the result depends on the database collation and sort rules. Use it only when the ordering and boundary strings are deliberate.

What is a half-open date range?

A half-open date range includes its start and excludes its next boundary. For August, use `>= 2026-08-01` and `< 2026-09-01`; September can then begin at the same boundary without overlap.

SQL Formatting