Filtering by Date Range in SQL
Filtering a month or quarter is everyday work. A half-open range keeps it correct and unambiguous.
"Show me everything from March" sounds simple, but date filters trip people up. Writing between '2024-03-01' and '2024-03-31' works until a column carries a time like 2024-03-31 14:00, which then gets dropped.
The reliable habit is a half-open range: greater than or equal to the start, and strictly less than the start of thenext period. You never have to know how many days a month has.
select *
from events
where happened_at >= date '2024-01-01'
and happened_at < date '2024-02-01'Safe filter for January 2024
The safest pattern for all rows in January 2024 is to include January 1 and exclude February 1. That works for plain dates and for timestamps with hours, minutes, and seconds.
select *
from events
where happened_at >= date '2024-01-01'
and happened_at < date '2024-02-01'Open the DuckDB playground with the matching dataset and query already filled in.
Schema · Garden ShopTable · orders6 columns · 24 rows
One row per order. Unshipped orders have a null shipped_date.
Find every order placed in March 2024. Return order_id, order_date, andstatus, sorted by order_date. Use a half-open range rather than hard-coding the last day of the month.
- Columns: order_id, order_date, status.
- Rows: 4 orders placed in March 2024.
- The half-open range >= '2024-03-01' and < '2024-04-01' captures the whole month.