Dates are compared like numbers
A DATE column holds a pure calendar day, while a timestamp column contains both the date and time. They compare just like numbers (earlier dates are smaller, later dates are bigger) when you write them in quotes as 'YYYY-MM-DD'. To pick a whole month, ask for dates on or after the first of that month and before the first of the next. Stopping before the 1st matters most for timestamp columns: asking for <= the 30th stops at midnight and quietly drops any rows recorded later that day.
A tiny example
In plain English: Show me the customers who joined during March 2019.
SELECT name, joined
FROM customers
WHERE joined >= '2019-03-01'
AND joined < '2019-04-01';Query result
| name | joined |
|---|---|
| Priya | 2019-03-04 |
| Tom | 2019-03-30 |
Priya and Tom are the only customers who joined sometime in March 2019.
Common questions
- Why does <= the last day of the month drop rows?
- A timestamp like 2025-06-30 14:30 is later than midnight at the start of June 30, so <= '2025-06-30' misses it. Use >= the first day and < the first day of the next month instead.
- Does this pattern still work with an index on the date column?
- Yes. Comparing the column directly against two dates lets the database use the index. Wrapping the column in a function, like DATE(ordered_at) = ..., usually can't.
Related SQL lessons
Ready for a break?
A fresh, bite-sized SQL puzzle waits every morning. Tap tiles, see real results. No terminal, no setup, no timers.
Try today's free SQL puzzle