sql · level 2 · unit 7 · lesson 3
- meet it
- guess first
- your turn
- fix it
- nailed it
Tap a green word if you get lost.
meet it
moves a date, and counts the days between.
date('2026-04-01', '+14 days') gives '2026-04-15'. The second part is a : it moves the date, and it knows how long each month is. '-7 days' and '+1 month' work too.
To count the days between two dates, turn each into a day number with julianday, then subtract. julianday('2026-05-01') - julianday('2026-04-15') is 16.
SQLite can read the real date, but then answers change every day. Reports here use a fixed today, '2026-05-01', so they give the same answer every run.
SELECT l.id, l.due,
julianday('2026-05-01') - julianday(l.due) AS days_late
FROM loans l
WHERE l.returned IS NULL AND l.due < '2026-05-01'
ORDER BY days_late DESC;julianday gives decimals when a time of day is included. Plain dates always give whole days.
guess first
A three-week loan. When is it due?
Guess before you run it. Wrong guesses are how you learn, and nothing is marked.
SELECT date('2026-04-20', '+21 days') AS due;your turn
Your turn. Set a loan period.
Type a number of days in the blank, like 7 or 14. Each loan then shows the date it would be due back.
result
Your answer appears here as a table.
fix it
Mia was 6 days late, not minus 6. Fix it!
result
Your answer appears here as a table.
- Which date is later: the due date, or the day the book came back?
- Subtract the earlier date from the later one: returned minus due.
- Swap them to julianday(l.returned) - julianday(l.due), then press run.
nailed it
you counted the days. :D
date(d, '+14 days')moves a date with a modifier.julianday(later) - julianday(earlier)counts the days between.- Use a fixed today, so answers never change.
new words in your collection
- date()
- modifier
- julianday
This lesson isn’t saved. A free account remembers every lesson you finish.