sql · level 2 · unit 7 · lesson 2
- meet it
- guess first
- your turn
- fix it
- nailed it
Tap a green word if you get lost.
meet it
Dates in sort and compare in time order.
SQLite has no special date type. Dates are stored as text in ISO format: year, month, day, like '2026-04-15'. Because the biggest part comes first, A to Z order is also time order.
So <, > and ORDER BY work on dates as they are. keeps every date in April, both ends included.
pulls out the year and month, like '2026-04'. Group by it to count loans month by month.
SELECT strftime('%Y-%m', borrowed) AS month, COUNT(*) AS loans
FROM loans
GROUP BY month
ORDER BY month;Always write two digits for the month and day, like '2026-04-05', or the order breaks.
guess first
The first ten days of April. Which loans?
Guess before you run it. Wrong guesses are how you learn, and nothing is marked.
SELECT b.title, l.borrowed
FROM loans l
JOIN books b ON l.book_id = b.id
WHERE l.borrowed BETWEEN '2026-04-01' AND '2026-04-10'
ORDER BY l.borrowed;your turn
Your turn. List one month's loans.
Type 2026-03 or 2026-04 between the quotes to list the loans that started in that month.
result
Your answer appears here as a table.
fix it
Four loans started in April, but none show. Fix it!
result
Your answer appears here as a table.
- Compare
'2026-4-1'with'2026-04-08'character by character. Which comes first, 4 or 0? - Dates are text, so they only sort in time order when every month and day has two digits.
- Change '2026-4-1' to '2026-04-01', then press run.
nailed it
your dates sort in time order. :D
- Store dates as ISO text, like
'2026-04-15'. BETWEENincludes both ends of the range.strftime('%Y-%m', date)pulls out the year and month.
new words in your collection
- ISO format
- BETWEEN
- strftime
This lesson isn’t saved. A free account remembers every lesson you finish.