Problem
Task
Return orders created during January 2019 using a range condition.
Schema
Table Schema
orders(id, customer_id, total_amount, created_at)Input
Sample Data
| id | customer_id | total_amount | created_at |
|---|---|---|---|
| 201 | 1 | 72 | 2019-01-01 |
| 202 | 2 | 88.5 | 2019-01-31 |
| 203 | 3 | 91 | 2019-02-01 |
Output
Expected Output
| id | customer_id | total_amount | created_at |
|---|---|---|---|
| 201 | 1 | 72 | 2019-01-01 |
| 202 | 2 | 88.5 | 2019-01-31 |
Answer
Check Your Solution
Show Answer and Explanation
Correct Answer
SELECT *
FROM orders
WHERE created_at >= '2019-01-01'
AND created_at < '2019-02-01'
ORDER BY created_at, id;Explanation
The start date is inclusive and the next month boundary is exclusive. This pattern also works when created_at contains time values. ORDER BY keeps the displayed output deterministic.
Common Mistakes
- Using created_at <= '2019-01-31' when the column may include times later that day.
- Applying a function to every row when a range condition is enough.
- Using OR instead of AND for the two date boundaries.
Concepts
Related Concepts
Date Ranges
WHERE
Sargable Conditions
Date Range
Comparison
Next practice