Learn SQL: Date functions
Calculating with dates
"How many books are overdue? And for how long?" Sanne has a pile of reminders ready, but doesn't know who to send them to. Today is 15 November 2025.
Taking a piece out of a date
Dates are stored as text in the form 'year-month-day'. With strftime you take a piece out: '%Y' is the year, '%m' the month, '%d' the day.
Careful: the result is text, with a leading zero. March is '03', not 3.
SELECT loaned_on, strftime('%Y', loaned_on) AS year, strftime('%m', loaned_on) AS month FROM loans| loaned_on | year | month |
|---|---|---|
| 2024-11-04 | 2024 | 11 |
| 2024-12-02 | 2024 | 12 |
| 2025-01-08 | 2025 | 01 |
| 2025-01-20 | 2025 | 01 |
| 2025-02-03 | 2025 | 02 |
| 2025-02-15 | 2025 | 02 |
| 2025-03-01 | 2025 | 03 |
| 2025-03-12 | 2025 | 03 |
Calculating with days
date(date, '+21 days') adds days (or takes them off with '-7 days'). You get the difference between two dates with julianday: it turns a date into a day number, and you can subtract those from each other.
If one of the two dates is NULL, as with a book that isn't back yet, the result is NULL too.
SELECT loaned_on, date(loaned_on, '+21 days') AS due_on, julianday(returned_on) - julianday(loaned_on) AS days_out FROM loans| loaned_on | due_on | days_out |
|---|---|---|
| 2024-11-04 | 2024-11-25 | 18 |
| 2024-12-02 | 2024-12-23 | 25 |
| 2025-01-08 | 2025-01-29 | 14 |
| 2025-01-20 | 2025-02-10 | 30 |
| 2025-02-03 | 2025-02-24 | 21 |
| 2025-02-15 | 2025-03-08 | 9 |
| 2025-03-01 | 2025-03-22 | 20 |
| 2025-03-12 | 2025-04-02 | 45 |
Try it yourself
Today is 15 November 2025. Which books aren't back yet and are already overdue? Show id, member_id and due_on.
Ready for more?
The full lesson has 7 exercises, from easy to challenging, with hints, points and a streak you can build.
Start the lesson