Have you ever missed records while filtering dates in SQL?
Imagine your manager asks you to prepare a report showing all orders placed on 7 November 2026.
Your "orders" table contains both dates and timestamps, such as "2026-11-07 14:30:00".
You decide to use BETWEEN to filter the records.
But will your query return all orders placed on that day?
Let's look at the data.
orders
| order_id | order_date | amount |
|---|---|---|
| 101 | 2026-11-07 09:30:00 | 5000 |
| 102 | 2026-11-07 14:45:00 | 3000 |
| 103 | 2026-11-07 22:15:00 | 7000 |
| 104 | 2026-11-08 08:00:00 | 4000 |
Your Challenge
Write an SQL query to:
- Retrieve all orders placed on 7 November 2026.
- Include orders placed at any time during that day.
- Exclude orders placed on 8 November 2026.
- Use a date range condition to filter the records.
Think before you scroll!
The SQL Solution
SELECT
order_id,
order_date,
amount
FROM orders
WHERE order_date >= '2026-11-07'
AND order_date < '2026-11-08';
Expected Output
| order_id | order_date | amount |
|---|---|---|
| 101 | 2026-11-07 09:30:00 | 5000 |
| 102 | 2026-11-07 14:45:00 | 3000 |
| 103 | 2026-11-07 22:15:00 | 7000 |
Why is this useful?
Date filtering is common in real-world SQL projects.
You can use this approach to find:
- Daily sales transactions
- Customer registrations
- Employee attendance records
- Orders placed during a specific date
- Daily revenue for business reports
One Important Thing to Remember
BETWEEN includes both boundary values.
When you write:
WHERE order_date BETWEEN '2026-11-07'
AND '2026-11-07'
Many SQL databases interpret the ending date as midnight at the start of that day when comparing against a timestamp.
As a result, orders placed later that day may be missed.
Using ">= start_date AND < next_date" is a reliable approach for timestamp-based date filtering.
Your Turn: Can you solve the same problem using BETWEEN without missing any orders?
Share your SQL query in the comments. Let's learn together!
Top comments (1)
Answering the closing question honestly: you can write it with BETWEEN, but only by naming the last representable instant of the day, and that is exactly why the half-open range is the better habit rather than just a workaround.
BETWEEN '2026-11-07' AND '2026-11-07 23:59:59'looks right and has a hole in it: an order at 23:59:59.500 falls through. You can close the hole with'2026-11-07 23:59:59.999999', but now the query is betting on the column's fractional-seconds precision, which differs across engines (MySQL DATETIME vs DATETIME(6), SQL Server's datetime rounding to .003). The correct BETWEEN endpoint is a property of the storage type, not of the day - easy to get silently wrong, and silent wrongness in a date filter is the worst kind because the report still looks plausible.The deeper rule: BETWEEN is safe on discrete domains. If the column were an integer date like 20261107, inclusive-inclusive is exact because there is a finite next value. Timestamps are effectively continuous, so the only exact shape is [start, next):
>= '2026-11-07' AND < '2026-11-08'. Half-open ranges also compose - a month is>= '2026-11-01' AND < '2026-12-01', and day ranges tile with no gaps and no overlaps, which BETWEEN ranges never do cleanly.Two footnotes for the challenge table itself:
CAST(order_date AS DATE) = '2026-11-07'also works, but on most engines it is non-sargable and skips the index, while the >= / < form keeps it. And "the column contains both dates and timestamps" is worth flagging on its own - a bare'2026-11-07'compares as midnight, so mixed-precision values in one column are where this class of bug usually starts.