AI Hints
Homeโ€บSQL Tutorialโ€บWorking with Dates
SQL ยท Lesson 29 of 126

Date Maths

Adding days, counting days.

โฑ 2 min readโ–ถ Video at 40:42๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

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.

Date Maths
From the video: 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.

query.sqlsql
SELECT order_id,
       order_date,
       date(order_date, '+14 days') AS return_by
FROM orders
LIMIT 3;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ 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.

query.sqlsql
SELECT first_name,
       joined,
       julianday('2026-01-01') - julianday(joined) AS days
FROM customers
ORDER BY days DESC
LIMIT 3;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ 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.

date('now')today's date
date(d, '+7 days')a week later
date(d, '-1 month')a month earlier
julianday(a) - julianday(b)days between

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
Next up: Practice: Busy Seasons โ€” dates in real questions