How to use BETWEEN in SQL

Q: BETWEEN with dates

Works with dates. Both endpoints are inclusive. For datetime columns, BETWEEN '2024-01-01' AND '2024-12-31' misses rows on 2024-12-31 after midnight. Use created_at >= '2024-01-01' AND created_at < '2025-01-01' for clarity.

Filter rows within a range of values.

BETWEEN with numbersAll databases

SELECT name, age
FROM users
WHERE age BETWEEN 18 AND 65;

BETWEEN is inclusive — returns rows where age >= 18 AND age <= 65.

BETWEEN with datesAll databases

SELECT order_id, total, created_at
FROM orders
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

Works with dates. Both endpoints are inclusive.

For datetime columns, BETWEEN '2024-01-01' AND '2024-12-31' misses rows on 2024-12-31 after midnight. Use created_at >= '2024-01-01' AND created_at < '2025-01-01' for clarity.

NOT BETWEENAll databases

SELECT name, age
FROM users
WHERE age NOT BETWEEN 18 AND 65;

Returns rows outside the range — age < 18 OR age > 65.

Common Mistakes

→BETWEEN is inclusive on both ends
→Date BETWEEN can miss end-of-day rows for DATETIME columns — use explicit >= and < instead

Common Mistakes

Related Topics