QuerydrillBETWEEN
WHERE amount BETWEEN 10 AND 100 is shorthand for amount >= 10 AND amount <= 100: both boundaries are included. It works for numbers, dates and text. For timestamps, prefer >= start AND < next_day, because BETWEEN '2025-01-31' AND '2025-01-31 23:59:59' misses nothing only if you spell the end time out.
Syntax
WHERE column BETWEEN low AND highExamples
SELECT id, amount FROM orders WHERE amount BETWEEN 50 AND 100;Result: Orders of 50, 100 and everything in between
SELECT * FROM orders WHERE order_date BETWEEN '2025-01-01' AND '2025-01-31';Result: January orders when order_date is a plain date
If order_date has a time part, an order at 2025-01-31 14:00 is excluded because it is greater than '2025-01-31'.
SELECT * FROM orders WHERE order_date >= '2025-01-01' AND order_date < '2025-02-01';Result: January orders, correct for both dates and timestamps
The half-open range is the safe idiom.
Common mistakes
- Writing BETWEEN 100 AND 10 (high first) returns no rows; the lower value must come first.
- Using BETWEEN on a timestamp column with a date as the upper bound silently drops the last day; use < the day after.
- Forgetting that both ends are inclusive and double counting rows at the boundary when ranges are chained (0-100, 100-200); use >= and < instead.
Practice BETWEEN
These drills check your answer on live data, free, no account needed.
- Small ordersReturn the id and amount of every order under 100, whatever the status.
- Filter rowsReturn the name and salary of employees who earn more than 90,000.
Questions
Is BETWEEN inclusive?
Yes, in every SQL database BETWEEN includes both the low and the high value.
How do I filter by date range in SQL?
For a date column, WHERE d BETWEEN '2025-01-01' AND '2025-01-31'. For a timestamp column, WHERE d >= '2025-01-01' AND d < '2025-02-01' so the whole last day is included.
Related
- WHEREUse WHERE to keep only the rows that match a condition: equals, greater than, text matches, NULL checks, and combinations with AND, OR and NOT.
- AND, OR, NOTHow to combine WHERE conditions with AND, OR and NOT, why AND binds before OR, how parentheses fix it, and how NULL changes the result of a logical test.
- Date functionsGet today's date, extract the year or month, group by month, add days and compute date differences in SQLite (strftime, julianday) and PostgreSQL (EXTRACT).
- INWHERE column IN (...) keeps rows whose value appears in a list or in a subquery result. Examples, NOT IN, the NULL trap, and when to use EXISTS instead.