SQL BETWEEN: Inclusive Ranges and the Date Trap Explained
SQL BETWEEN filters values inside a range, and both ends are included. Learn how it works with numbers, text and dates, why a month report can silently miss its last day, how NULLs and reversed bounds behave, and the index-friendly date query to use instead, with every query run.
- SQL BETWEEN is inclusive:
x BETWEEN a AND bmeansx >= a AND x <= b. - On date-time columns,
BETWEEN '2026-11-01' AND '2026-11-30'misses everything after midnight on the 30th. Our query lost 2 of 6 November orders. - Use a half-open range for dates:
>= '2026-11-01' AND < '2026-12-01'. - Wrapping the column in
date()fixed the result but made the query about 60 times slower by skipping the index.
Range filters are everywhere: orders above a threshold, ages in a bracket, sales in a month. SQL BETWEEN makes them short and readable. It also hides one of the most common reporting bugs in SQL, a monthly total that quietly leaves out the last day. This guide shows exactly how BETWEEN behaves and when to use something else.
Every query below ran on SQLite 3.50.4 and the output is copied from the run. BETWEEN is standard SQL, and its inclusive rule is the same in MySQL, PostgreSQL, SQL Server and Oracle; we flag where date handling differs.
What does BETWEEN do in SQL?
SELECT columns
FROM table_name
WHERE column BETWEEN low AND high;
It keeps rows whose value is greater than or equal to low and less than or equal to high. Both boundary values are included. It works with numbers, dates, times and text, anywhere the database knows how to compare values.
The sample data
CREATE TABLE orders (id INTEGER PRIMARY KEY, amount INT,
created_at TEXT, customer TEXT);
INSERT INTO orders (amount, created_at, customer) VALUES
(50, '2026-11-01 09:15:00', 'Asha'), (100, '2026-11-10 00:00:00', 'Ravi'),
(150, '2026-11-15 18:45:00', 'Meera'), (200, '2026-11-30 00:00:00', 'Kabir'),
(250, '2026-11-30 14:20:00', 'Lina'), (300, '2026-12-01 00:00:00', 'Zoya'),
(NULL,'2026-11-20 10:00:00', 'Dev');
Seven orders: six in November, one on 1 December, two on the last day of November, and one with a missing amount.
BETWEEN with numbers
SELECT id, amount FROM orders WHERE amount BETWEEN 100 AND 250;
-- id | amount
-- 2 | 100
-- 3 | 150
-- 4 | 200
-- 5 | 250
Both 100 and 250 are in the result, confirming that the boundaries are included. For whole numbers this is exactly what most people expect. For prices and other decimals, remember that 250.01 would be excluded.
BETWEEN vs >= and <=
SELECT COUNT(*) FROM orders WHERE amount >= 100 AND amount <= 250; -- 4
Same four rows. BETWEEN is shorthand, not a different operation, and the optimizer treats both forms the same way. Choose BETWEEN when the range is naturally closed at both ends and you want readability; write the comparisons out when you need one end open, as we will for dates.
Reversed bounds return nothing
SELECT COUNT(*) FROM orders WHERE amount BETWEEN 250 AND 100; -- 0
No error, just zero rows. BETWEEN 250 AND 100 asks for values that are at least 250 and at most 100, which is impossible. This bites when the bounds come from user input or a date picker that let someone choose the end before the start. Validate or swap the values in your application. SQL Server and PostgreSQL behave the same way. PostgreSQL also offers BETWEEN SYMMETRIC, which sorts the bounds for you (we did not run it here).
NOT BETWEEN and NULL values
SELECT id, amount FROM orders WHERE amount NOT BETWEEN 100 AND 250;
-- id | amount
-- 1 | 50
-- 6 | 300
Four rows are inside the range and two outside, which adds up to six. The seventh order, Dev’s with a NULL amount, appears in neither result. Any comparison with NULL is unknown, so the row fails both BETWEEN and NOT BETWEEN. If missing values should count somewhere, add OR amount IS NULL explicitly. Our SQL NULL values guide explains this three-valued logic.
The date trap: missing the last day
Here is the November report many people write first:
SELECT id, created_at FROM orders
WHERE created_at BETWEEN '2026-11-01' AND '2026-11-30';
-- 1 | 2026-11-01 09:15:00
-- 2 | 2026-11-10 00:00:00
-- 3 | 2026-11-15 18:45:00
-- 7 | 2026-11-20 10:00:00
Only four rows. Both orders from 30 November are gone, including the one placed exactly at midnight. The upper bound '2026-11-30' means the very start of that day. In SQLite, where dates are text, '2026-11-30 00:00:00' sorts after '2026-11-30' because it is longer, so even midnight is excluded. In databases with real date-time types, such as SQL Server, the bare date becomes midnight, so a midnight order would be included but anything later on the 30th would still be missed, as the Microsoft documentation warns. Either way, a whole day of sales can vanish from a monthly report without any error.
On our larger test table of 300,000 random events, the same mistake dropped 818 rows from November: 23,893 instead of the correct 24,711.
The fix: half-open ranges
SELECT id, created_at FROM orders
WHERE created_at >= '2026-11-01' AND created_at < '2026-12-01';
-- 1 | 2026-11-01 09:15:00
-- 2 | 2026-11-10 00:00:00
-- 3 | 2026-11-15 18:45:00
-- 4 | 2026-11-30 00:00:00
-- 5 | 2026-11-30 14:20:00
-- 7 | 2026-11-20 10:00:00
All six November orders, and Zoya’s order on 1 December correctly excluded. The pattern is: greater than or equal to the start of the period, strictly less than the start of the next period. It works for any precision, seconds, milliseconds or microseconds, and for months of any length, leap years included. Adjacent periods never overlap and never leave gaps, which matters when you compare month against month.
Tip: for any date-time column, default to >= start AND < next_start. Keep BETWEEN for columns that hold only dates or whole numbers.
End-of-day workarounds and their problems
-- end-of-day string
WHERE created_at BETWEEN '2026-11-01' AND '2026-11-30 23:59:59'
-- wrap the column
WHERE date(created_at) BETWEEN '2026-11-01' AND '2026-11-30'
Both returned the correct six rows here, but each has a catch. 23:59:59 misses events at 23:59:59.500 when the column stores fractions of a second, and people often get the time string wrong. In SQL Server, '23:59:59.999' rounds up to the next day for the datetime type, a classic surprise. Wrapping the column in date() is correct but slow, as the next section shows.
Performance: keep the column bare
We indexed the timestamp column of the 300,000-row table and timed the November count (best of three):
| Filter | Rows | Time | Plan |
|---|---|---|---|
ts >= '2026-11-01' AND ts < '2026-12-01' |
24,711 | 1.93 ms | Index search |
date(ts) BETWEEN '2026-11-01' AND '2026-11-30' |
24,711 | 119.88 ms | Full table scan |
ts BETWEEN '2026-11-01' AND '2026-11-30' |
23,893 (wrong) | 1.71 ms | Index search |
Applying a function to the column hides it from the index, so the database must compute date() for every row. The half-open range is both correct and fast, about 60 times faster than the wrapped version. Our SQL indexes guide covers why functions on columns block index use, and SQL date functions shows how to compute period boundaries.
BETWEEN with text
SELECT customer FROM orders WHERE customer BETWEEN 'A' AND 'L' ORDER BY customer;
-- Asha, Dev, Kabir
SELECT customer FROM orders WHERE customer BETWEEN 'A' AND 'Lz' ORDER BY customer;
-- Asha, Dev, Kabir, Lina
Text is compared character by character, so 'Lina' is greater than 'L' and falls outside the first range, even though it “starts with L”. Using a higher bound like 'Lz' is a fragile hack that fails for names such as “Lü” in some collations. For “names from A to L”, write customer >= 'A' AND customer < 'M', the same half-open idea. Case and accent rules depend on the collation, and our SQL LIKE guide covers prefix matching.
BETWEEN across databases
- All major databases: inclusive at both ends; reversed bounds return no rows.
- SQLite: dates are usually text, so comparisons are string comparisons. Use ISO 8601 format so that string order equals time order.
- SQL Server: a date string becomes midnight;
datetimerounds to about 3 ms. - PostgreSQL: supports
BETWEEN SYMMETRIC; withtimestampcolumns, a date bound means midnight. - MySQL: comparing a
DATETIMEwith a date string also uses midnight, so the last-day trap applies.
Only the SQLite behaviour was run for this article; the other notes follow each vendor’s documentation.
SQL BETWEEN rules of thumb
- Use BETWEEN for closed ranges of integers and pure dates.
- Use
>= start AND < next_startfor timestamps. - Never wrap an indexed column in a function inside the filter.
- Validate that start is not after end.
- Decide explicitly where NULLs belong.
- Prefer half-open ranges for text prefixes too.
Final thoughts
SQL BETWEEN is simple: inclusive on both ends, shorthand for two comparisons. That simplicity is exactly what makes it dangerous on timestamp columns, where a bare end date means the start of the last day. Use BETWEEN for numbers and pure dates, switch to half-open ranges for date-times and text prefixes, keep indexed columns unwrapped, and your reports will include every row they should. For paging through large result sets, see SQL pagination.
Frequently asked questions
Is SQL BETWEEN inclusive?
Yes. BETWEEN includes both the lower and the upper value, so x BETWEEN 1 AND 10 matches 1, 10 and everything in between. It is the same as x >= 1 AND x <= 10.
Why does BETWEEN miss rows on the last day?
On a date time column, a bare end date means midnight at the start of that day, so later times on that day fall outside the range. Use a half-open range that ends before the next day instead.
What happens if the BETWEEN values are reversed?
The query returns no rows and no error, because no value can be at least the higher number and at most the lower one. Make sure the smaller value comes first.
Does BETWEEN include NULL values?
No. A NULL never satisfies BETWEEN or NOT BETWEEN, because comparisons with NULL are unknown. Add OR column IS NULL if those rows should be included.
Is BETWEEN faster than greater than and less than?
No. BETWEEN is shorthand for the two comparisons and performs the same. What matters for speed is keeping the column free of functions so an index can be used.
Keep learning
Need help with testing, WordPress or SEO?
We help teams ship better software and grow their sites. Tell us what you are working on and we will suggest a clear next step.