SQL query between two dates filters rows where a date or datetime column falls within a specified start and end range, most commonly using BETWEEN or >=/< comparisons. The critical pitfall is inclusive boundary behavior when time components are present.
SELECT * FROM tbl WHERE datecol >= '20210913 05:00' AND datecol < '20210914 05:00';Why off-by-one errors occur with BETWEEN on dates
The BETWEEN operator is inclusive of both endpoints. When a datetime column contains time components, a query such as datecol BETWEEN '2021-09-13' AND '2021-09-14' includes rows from midnight of the 13th through midnight of the 14th — not the entire 24-hour period ending at 23:59:59. This causes missing records or unintended inclusion.
| Cause | Example | Resulting problem |
|---|---|---|
BETWEEN is inclusive |
WHERE datecol BETWEEN '2021-09-13' AND '2021-09-14' |
Rows with 2021-09-14 00:00:00 are included |
| No time component in literal | Same as above | Assumes 00:00:00, misses later times on the 13th |
| Time column separate | WHERE Date BETWEEN '2021-09-13' AND '2021-09-14' AND Time BETWEEN '05:00' AND '04:59' |
Logical clash: lower bound higher than upper |
| Implicit conversion | BETWEEN '09/13/2021' AND '09/14/2021' |
Regional date format ambiguity |
Correct filtering patterns
Choose the pattern that matches your column type and the exact range you need.
- For a full day (00:00:00 to 23:59:59): Use
datecol >= '2021-09-13' AND datecol < '2021-09-14'(half-open interval). This works for bothDATEandDATETIMEcolumns. - For an inclusive range covering both days:
datecol BETWEEN '2021-09-13' AND '2021-09-14'— only safe if the column isDATEwithout time. - For a specific time span (e.g. 05:00 to 04:59 next day): Use two conditions on the same datetime column:
datecol >= '2021-09-13 05:00:00' AND datecol < '2021-09-14 05:00:00'.
Common mistakes
- Using BETWEEN with datetime expecting a full day:
BETWEEN '2021-09-13' AND '2021-09-13'only captures midnight of the 13th. Fix: use>= '2021-09-13' AND < '2021-09-14'. - Writing BETWEEN with upper bound smaller than lower bound:
BETWEEN '2021-09-14' AND '2021-09-13'returns zero rows. Always order ascending. - Separating date and time into two columns and using BETWEEN on both: Combines logically poorly. Better to merge into a single datetime column.
- Assuming BETWEEN is exclusive on the end: Some dialects (e.g., PostgreSQL with
daterange) are exclusive; know your DBMS behavior.
Frequently Asked Questions
What is the difference between `BETWEEN` and `>= AND <=` in date queries?
Answer: Both are inclusive of both boundaries. `BETWEEN` is syntactic sugar for `column >= start AND column <= end`. Query plans are identical. For exclusive ranges, use `column >= start AND column < end`.
-- PostgreSQL inclusive equivalent
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';
-- Same as:
SELECT * FROM orders WHERE order_date >= '2023-01-01' AND order_date <= '2023-01-31';
When should I use the `DATE()` function (or `CAST`) in date range queries?
Answer: Use `DATE()` to strip time components when comparing a TIMESTAMP column against a date-only literal, avoiding boundary errors. However, this prevents index usage. Prefer an explicit range with `>=` and `<` on the original column.
-- MySQL: correct for TIMESTAMP column, but may not use index
SELECT * FROM logs WHERE DATE(created_at) = '2023-01-01';
-- Equivalent with index-friendly range:
SELECT * FROM logs WHERE created_at >= '2023-01-01' AND created_at < '2023-01-02';
How do I fix error "ORA-01843: not a valid month" when querying dates?
Answer: Always use explicit conversion with `TO_DATE()` or ANSI date literals `DATE 'YYYY-MM-DD'` instead of relying on implicit conversion and session NLS settings.
-- Incorrect (if NLS_DATE_FORMAT='DD-MON-YY')
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';
-- Correct
SELECT * FROM orders WHERE order_date BETWEEN DATE '2023-01-01' AND DATE '2023-01-31';
Does `BETWEEN` work the same across AWS RDS (MySQL), GCP Cloud SQL (PostgreSQL), and Azure SQL Database?
Answer: Yes, `BETWEEN` is ANSI SQL and behaves identically: inclusive both ends. Key differences: MySQL accepts 'YYYY-MM-DD' strings; PostgreSQL requires DATE 'YYYY-MM-DD'; SQL Server accepts 'YYYYMMDD'. Timezone handling varies per cloud.
-- Azure SQL (MSSQL)
SELECT * FROM orders WHERE order_date BETWEEN '20230101' AND '20230131';
-- Cloud SQL PostgreSQL
SELECT * FROM orders WHERE order_date BETWEEN DATE '2023-01-01' AND DATE '2023-01-31';
-- AWS RDS MySQL
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';
What is the fastest way to query date ranges in large tables with billions of rows?
Answer: Use a clustered index on the date column, partition tables by date range, and use the half-open pattern (column >= start AND column < end) to avoid boundary functions that skip index seek. Verify with EXPLAIN ANALYZE.
-- Efficient inclusive-exclusive pattern
SELECT * FROM orders
WHERE order_date >= '2023-01-01'::date
AND order_date < '2023-02-01'::date;
