Date Maths
Adding days, counting days.
Helen lets customers return a book within fourteen days. For each order, what's the last day to return it? And how long has each customer been with the shop? Both are date maths.

- Returns within 14 days
- How long has a customer been here?
- Both need date maths
Add days to a date
The date function can move a date forwards or backwards. Date of the order date, plus fourteen days, gives the return deadline. It even handles the end of a month correctly. Run it. An order on the twenty fourth of November can be returned until the eighth of December.
SELECT order_id,
order_date,
date(order_date, '+14 days') AS return_by
FROM orders
LIMIT 3;โโโโโโโโโโโโฌโโโโโโโโโโโโโฌโโโโโโโโโโโโโ โ order_id โ order_date โ return_by โ โโโโโโโโโโโโผโโโโโโโโโโโโโผโโโโโโโโโโโโโค โ 1001 โ 2025-03-06 โ 2025-03-20 โ โ 1002 โ 2024-11-24 โ 2024-12-08 โ โ 1003 โ 2024-06-22 โ 2024-07-06 โ โโโโโโโโโโโโดโโโโโโโโโโโโโดโโโโโโโโโโโโโ
Days between two dates
To count the days between two dates, SQLite turns each date into a day number, using julianday. Then you just subtract. Here we measure how long each customer had been with the shop on New Year's Day twenty twenty six. Run it. Rosa has been a customer for over a thousand days.
SELECT first_name,
joined,
julianday('2026-01-01') - julianday(joined) AS days
FROM customers
ORDER BY days DESC
LIMIT 3;โโโโโโโโโโโโโโฌโโโโโโโโโโโโโฌโโโโโโโโโ โ first_name โ joined โ days โ โโโโโโโโโโโโโโผโโโโโโโโโโโโโผโโโโโโโโโค โ Rosa โ 2023-01-04 โ 1093.0 โ โ Leo โ 2023-01-07 โ 1090.0 โ โ Will โ 2023-01-07 โ 1090.0 โ โโโโโโโโโโโโโโดโโโโโโโโโโโโโดโโโโโโโโโ
Today, in your own queries
In your own work, you'll often compare with today's date. In SQLite, that's date, with the word now in quotes. We use fixed dates in this course, so your answers match the video exactly, whenever you watch it.
Summary
You can add days, and count the days between dates. Let's put all of the date tools together in a practice round.
Key takeaways- date(d, '+14 days')
- julianday() for differences
- date('now') is today