Forgly

SQL to select rows between two dates

Select rows between two dates in SQL — filter a date or timestamp column by a date range using BETWEEN or a half-open range, with the timestamp gotcha explained.

BETWEEN — works everywhere (inclusive of both endpoints)
SELECT *
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';

BETWEEN includes both '2024-01-01' and '2024-01-31'. Fine for a pure DATE column.

Half-open range — correct for timestamps
SELECT *
FROM orders
WHERE created_at >= '2024-01-01'
  AND created_at <  '2024-02-01';

A timestamp like '2024-01-31 14:00' is later than '2024-01-31 00:00:00', so BETWEEN ... AND '2024-01-31' would drop it. >= start AND < next-day captures the whole last day.

How to select rows between two dates

To select rows between two dates in SQL, filter the date column with BETWEEN or a pair of comparisons. BETWEEN reads cleanest but is inclusive on both ends, so on a timestamp column it silently drops the last day — use a half-open range (>= start AND < next day) there. Copy-paste examples for both below.

How it works

  • BETWEEN a AND b is shorthand for col >= a AND col <= b — both ends are included.
  • On a timestamp column the upper bound '2024-01-31' means midnight, so rows later that day are excluded — use < the next day instead.
  • Comparing the bare column (not a function of it) lets the database use an index on the date column.

Tip

For date ranges on a timestamp column, prefer the half-open pattern (>= start AND < end) over BETWEEN to avoid silently dropping the last day.

Related SQL queries

Frequently asked questions

How do I select rows between two dates in SQL?

To select rows between two dates in SQL, filter the date column with BETWEEN or a pair of comparisons. BETWEEN reads cleanest but is inclusive on both ends, so on a timestamp column it silently drops the last day — use a half-open range (>= start AND < next day) there. Copy-paste examples for both below. BETWEEN a AND b is shorthand for col >= a AND col <= b — both ends are included. On a timestamp column the upper bound '2024-01-31' means midnight, so rows later that day are excluded — use < the next day instead. Comparing the bare column (not a function of it) lets the database use an index on the date column.

Does this work in PostgreSQL, MySQL, SQL Server, and SQLite?

Yes — this page lists the query for each dialect, since the syntax can differ between database engines.

Can I generate this query for my own tables?

Yes. Describe what you want in plain English with Forgly's free AI SQL Generator and it writes the query for your dialect.