SQL
Retrieve Data for Specific Date Ranges
Learn essential SQL techniques for filtering records based on dynamic or fixed date ranges, such as the last 7 days, current month, or a specific period, using common database functions.
-- Retrieve data for a specific fixed date range
SELECT id, item_name, order_date
FROM sales
WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';
-- Retrieve data for the last 7 days (PostgreSQL/MySQL syntax)
SELECT id, item_name, order_date
FROM sales
WHERE order_date >= CURRENT_DATE - INTERVAL '7 days';
-- Retrieve data for the current month (PostgreSQL/MySQL syntax)
SELECT id, item_name, order_date
FROM sales
WHERE order_date >= DATE_TRUNC('month', CURRENT_DATE) AND order_date < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month';
How it works: Filtering data by date is a common requirement. This snippet provides three highly useful methods. The first uses `BETWEEN` for a fixed date range. The second retrieves records from the last 7 days using `CURRENT_DATE` and `INTERVAL` (syntax may vary slightly across databases like SQL Server or Oracle). The third fetches records for the entire current month, using `DATE_TRUNC` to get the start of the month and `INTERVAL` to define the end, which is robust for monthly reporting.