1. Home
  2. Blog
  3. Databases & SQL

Databases & SQL

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.

9 min read
SQL BETWEEN: inclusive ranges and the date trap explained

Key takeaways

  • SQL BETWEEN is inclusive: x BETWEEN a AND b means x >= 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; datetime rounds to about 3 ms.
  • PostgreSQL: supports BETWEEN SYMMETRIC; with timestamp columns, a date bound means midnight.
  • MySQL: comparing a DATETIME with 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_start for 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

Work with Stack Quora

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.

Keep reading

More guides on testing, AI & modern development

Enjoyed this article? Pick a topic and jump straight into our newest tutorials.

Artificial Intelligence

K Nearest Neighbors (KNN) Explained: How It Works, With Python Code

K nearest neighbors explained: the KNN algorithm, a from-scratch NumPy version, choosing k, feature scaling, distance metrics, regression and vector…

Read article →
Software Development

HTTP Cookies Explained: Secure, HttpOnly, SameSite and Scope

HTTP cookies explained: Set-Cookie, Path, Domain, expiry, Secure, HttpOnly, SameSite, prefixes, deleting cookies and a secure session recipe, tested locally.

Read article →
DevOps

Git Rebase Interactive: Squash, Fixup, Reword and Autosquash

Git rebase interactive explained step by step: pick, reword, squash, fixup, edit, drop, autosquash, conflicts and undoing a rebase with…

Read article →
Software Testing

Alpha vs Beta Testing: Key Differences, Exit Criteria and Checklist

Alpha vs beta testing compared: who tests, what each phase finds, entry and exit criteria, how to run a beta,…

Read article →
Test Automation

Pytest Monkeypatch: 10 Tested Examples for Env Vars, Time and APIs

Pytest monkeypatch explained with 10 passing tests: environment variables, functions, config, time, sleeps, paths, blocking the network and the wrong-target…

Read article →
Databases & SQL

SQL Auto Increment: MySQL, PostgreSQL, SQL Server and SQLite

SQL auto increment in MySQL, PostgreSQL, SQL Server and SQLite: syntax, reading the new ID, why gaps appear, ID reuse,…

Read article →
Databases & SQL

SQL BETWEEN: Inclusive Ranges and the Date Trap Explained

SQL BETWEEN explained with tested queries: inclusive ranges, reversed bounds, NULLs, the last-day date trap, half-open ranges and index-friendly date…

Read article →
Software Development

JavaScript Template Literals: 10 Examples Including Tagged Templates

JavaScript template literals explained with 10 tested examples: interpolation, multiline strings, escaping, tagged templates for safe HTML and SQL, and…

Read article →
Software Development

Python heapq: Priority Queues, Top N and Heaps Explained

Python heapq explained: heappush, heappop, heapify, priority queues with tie-breakers, max heaps, nlargest and merge, with real timings against sorting.

Read article →
Software Development

Python Walrus Operator (:=) Explained With 8 Real Examples

The Python walrus operator := explained with 8 tested examples: if checks, regex matches, while loops, comprehensions, scope rules, precedence…

Read article →
Artificial Intelligence

Softmax Function Explained: Formula, Python Code and Stable Version

The softmax function explained: formula, worked example, NumPy code, the stable version that avoids NaN, temperature, cross-entropy and softmax vs…

Read article →
Software Development

CORS Explained: Preflight Requests, Headers and How to Fix CORS Errors

CORS explained: origins, simple and preflight requests, the response headers your server must send, credentials, and how to fix common…

Read article →
DevOps

Git Diff Explained: Staged Changes, Branches and Commits

Git diff explained: read the output, compare unstaged and staged changes, commits and branches, two dots vs three dots, and…

Read article →
Test Automation

Pytest Xdist: Run Tests in Parallel and Cut Test Time

Run pytest tests in parallel with pytest-xdist: install, the -n option, distribution modes, real timings and how to fix shared-state…

Read article →
Software Testing

Verification vs Validation in Software Testing: Example and Table

Verification vs validation in software testing explained with a comparison table and a tested coupon example where every spec test…

Read article →
Databases & SQL

SQL CAST Explained: Convert Data Types With Tested Examples

SQL CAST explained with tested examples: text to number, truncation, integer division, numbers stored as text, dates, bad input and…

Read article →
Databases & SQL

SQL DISTINCT: How to Remove Duplicate Rows (and Its Traps)

SQL DISTINCT explained with tested queries: one and many columns, NULLs, COUNT DISTINCT, DISTINCT vs GROUP BY, the SUM DISTINCT…

Read article →
Software Development

JavaScript Reduce Explained: 12 Practical Examples

JavaScript reduce explained step by step with 12 practical examples: sums, tallies, grouping, Maps, pipelines, sequential promises and the empty…

Read article →
Artificial Intelligence

Levenshtein Distance: Formula, Worked Table and Python Code

Levenshtein distance explained: the edit operations, the dynamic programming table for kitten and sitting, Python code with two-row memory, normalised…

Read article →
DevOps

Gitignore: Patterns, Negation and Fixing Tracked Files

A tested gitignore guide: pattern rules, leading and trailing slashes, negation with !, the ignored parent folder trap, git check-ignore…

Read article →
Software Testing

Acceptance Criteria: Given When Then Examples and Checklist

How to write acceptance criteria testers can run: rule-based and Given When Then formats, a worked discount code example turned…

Read article →
Software Testing

State Transition Testing: Diagram, Table and Example

State transition testing explained with a login lockout example: states, events, the diagram, the state table, valid and invalid transitions,…

Read article →
Databases & SQL

Second Highest Salary in SQL: 5 Tested Ways and the Tie Trap

Find the second highest salary in SQL five ways: MAX subquery, LIMIT OFFSET, DENSE_RANK, a correlated subquery and per department,…

Read article →
Databases & SQL

SQL ROW_NUMBER: Top N per Group, Dedupe and Pagination

SQL ROW_NUMBER explained with tested queries: PARTITION BY, top N per group, deleting duplicates, pagination, the tie trap and how…

Read article →
Test Automation

Python Unittest: Assert Methods, setUp and Mocks With Examples

A practical Python unittest tutorial: TestCase, assert methods, setUp and tearDown, subTest, skips, expected failures and mock, with a test…

Read article →
Artificial Intelligence

Jaccard Similarity: Formula, Python, MinHash

Jaccard similarity measures overlap between two sets as intersection over union. Work the formula, compute it in Python for words…

Read article →
DevOps

Git Hooks: Pre-commit and Commit-msg Examples

Git hooks run scripts at key moments such as commit and push. See tested pre-commit, commit-msg and prepare-commit-msg hooks, sharing…

Read article →
Test Automation

API Testing in Python: Pytest + Requests Suite

Build an API testing suite in Python with pytest and requests: fixtures, status codes, headers, JSON schema validation, CRUD flows,…

Read article →
Software Testing

Test Design Techniques: 9 Methods + Examples

Test design techniques help you write fewer tests that catch more bugs. Learn 9 black-box, white-box and experience-based methods with…

Read article →
Artificial Intelligence

TF-IDF Explained: Formula and Worked Example

TF-IDF scores a word by how often it appears in a document and how rare it is across all documents….

Read article →
DevOps

Git Tag: Create, Push and Delete Release Tags

Git tag explained with tested commands: annotated vs lightweight tags, listing and sorting, pushing with follow-tags, deleting local and remote…

Read article →
Test Automation

Pytest Coverage: pytest-cov and Branch Gaps

Measure pytest coverage with pytest-cov: term-missing reports, branch coverage, HTML reports, pyproject settings and a fail-under gate for CI, with…

Read article →
Software Testing

Error Guessing in Testing: Checklist + Demo

Error guessing uses tester experience to predict where code breaks. Get a ready checklist of risky inputs, a step by…

Read article →
Artificial Intelligence

BM25 Explained: How Keyword Search Ranks Results, With Python Code and Hybrid Search

BM25 explained with a pure Python implementation we ran: the formula, IDF, term saturation, the k1 and b parameters, why…

Read article →
DevOps

Git Bisect Explained: Find the Commit That Broke Your Code, Manually or Automatically

Git bisect explained with a tested repository: find the one bad commit among 41 in 5 test runs, automate it…

Read article →
Test Automation

pytest Markers Explained: Custom Marks, skip, skipif, xfail and -m Filters

pytest markers explained with a tested suite: register custom marks, run only smoke tests with -m, combine filters with and,…

Read article →