SQL Date Queries: Date Ranges, EXTRACT, and DATE_DIFF Explained

Dates answer some of the most common business questions: what happened this month, how long did shipping take, and how many orders arrived each week? SQL can answer those questions directly when date columns are treated as dates.

In SQL for Files, you can practice date queries in the browser using guided order data or your own local files with DuckDB WASM.

For month ranges, a lower bound plus an exclusive upper bound is often safer than BETWEEN, especially when timestamp values enter the picture.

SELECT order_id, order_date
FROM orders
WHERE order_date >= DATE '2024-02-01'
  AND order_date < DATE '2024-03-01';

EXTRACT pulls out useful pieces like month, year, quarter, or day. This is helpful for grouped reports and quick checks.

SELECT EXTRACT(MONTH FROM order_date) AS order_month, COUNT(*) AS order_count
FROM orders
GROUP BY EXTRACT(MONTH FROM order_date)
ORDER BY order_month;

DATE_DIFF measures the distance between two dates. That makes it useful for shipping time, lead time, overdue tasks, retention, and subscription length.

SELECT order_id, DATE_DIFF('day', order_date, ship_date) AS days_to_ship
FROM orders
ORDER BY order_id;

1. Filter February orders with a precise date range.
2. Extract month values to build monthly counts.
3. Use DATE_DIFF to calculate days between order and shipment.