Why SQL `BETWEEN` catches both boundaries
In SQL, `BETWEEN` is inclusive: a value matches when it is greater than or equal to the lower bound and less than or equal to the upper bound. So `price BETWEEN 10 AND 20` includes rows priced exactly 10 or 20. This differs from the half-open ranges often used in programming, where the start is included but the end is excluded.
That final `=` matters most when a query uses dates and times.
The hidden time in a date filter
A date column may store a time as well as a calendar day. If a query asks for records between `'2025-04-01'` and `'2025-04-30'`, the upper value may be treated as midnight at the start of April 30. A record from later that day—say, 3 p.m.—is after that endpoint and may be left out, even though it belongs to April 30.
A reliable pattern is to include the beginning of the period and exclude the beginning of the next one: `timestamp >= '2025-04-01' AND timestamp < '2025-05-01'`. This captures every time on April 30 without relying on how finely the database stores fractions of a second.
Make the endpoints explicit
For plain numbers or dates without times, `BETWEEN` can be clear and convenient. For timestamps, thinking in terms of a closed start and open end helps avoid boundary surprises: the next period can begin exactly where the previous one stops, with no gap or duplicate at the seam.
