Rolling dates with CURRENT_DATE
Hardcoded dates like '2024-01-01' stay frozen, but real questions ask for "the last 30 days". CURRENT_DATE returns today's date dynamically, meaning it changes every day on its own. Subtract an INTERVAL (a length of time, like '7 days') to get a cutoff that moves with the calendar. This works on DATE columns (just a calendar day) and TIMESTAMP columns (a date plus a time). If dates are stored as text strings, convert them to a real date type first. Nobody memorizes every database's date syntax. Remember the pattern: today, minus an interval.
A tiny example
In plain English: Show me the coffee batches roasted within the last 7 days, counting back from today.
SELECT item, roasted_on
FROM coffee_batches
WHERE roasted_on >= CURRENT_DATE - INTERVAL '7 days';Query result
| item | roasted_on |
|---|---|
| House Blend | 3 days ago |
| Winter Roast | yesterday |
Only batches roasted within the last week appear. Run it tomorrow and the cutoff moves forward a day on its own.
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