Lesson 7.1

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 the next period. You never have to know how many days a month has.

For all of January 2024, include January 1 and exclude February 1. That works for plain dates and for timestamps with hours, minutes, and seconds.

Pattern

select *
from events
where happened_at >= date '2024-01-01'
  and happened_at <  date '2024-02-01'

PostgreSQL, DuckDB, and SQL Server syntax

PostgreSQL and DuckDB accept the typed literals in the example above. SQL Server does not use the date '2024-01-01' literal form; cast an unambiguous year-month-day value instead. The range logic stays the same: include the first boundary and exclude the next one.

SQL Server date range

select *
from orders
where order_date >= cast('2024-03-01' as date)
  and order_date <  cast('2024-04-01' as date)

This is different from wrapping the column in a function to extract its month or year. Keeping the column bare makes the boundary clear and gives the database a straightforward range predicate to evaluate.

Ready to run it?

Open the DuckDB playground with the matching dataset and query already filled in.

Try Date filter in the playground

Syntax reference

Need syntax details? See the SQL date functions reference

Schema · Garden Shop Table · orders 6 columns · 24 rows

Table · orders

One row per order. Unshipped orders have a null shipped_date.

6 columns · 24 rows

order_id int customer_id int order_date date shipped_date date status text coupon_code text

Your task

Find every order placed in March 2024. Return order_id, order_date, and status, sorted by order_date. Use a half-open range rather than hard-coding the last day of the month.

Garden Shop · SQL engine loading · Ctrl/⌘ + Enter to run·

Run your query to inspect the result.

Expected answer

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.

← Back to all lessons

Next · 7.2 Date math and lead times →

Reference

SQL Date Functions

Date parts, math, and truncation.

Cheatsheet

SQL Date and String Functions Cheatsheet

Everyday date and text functions.

Interview

SQL Date Range Interview Questions

Practice safe date filters, buckets, and lead-time reports.

Mistake