Skip to the content
Start

Date arithmetic

SQL · Unit 14: Text and dates

The neighborhood library has a single copy of each book, so it lends for 3 days. When is each book that's still out due?

DATE takes a date and, after it, a change written as text: a sign, a number, and the unit. It returns another date, already in YYYY-MM-DD.

Hannah has until today, January 15. Dylan's loan period ran out on the 13th. This way of adding days is SQLite's. In PostgreSQL, with a column of a date type, you write borrowed + 3.

SELECT member, borrowed,
  DATE(borrowed, '+3 days') AS due
FROM loans
WHERE returned IS NULL
ORDER BY due;

Returns

memberborroweddue
Dylan2026-01-102026-01-13
Hannah2026-01-122026-01-15
Ann2026-01-132026-01-16

The rest of the explanation is in the lesson, which is part of the full plan.

Exercises in this lesson

You do them in the app, which checks them on the spot and explains why.

  1. 1. Predict the output

    Suppose a book had gone out on December 30, 2025, with the same 3-day loan period. What does this query return?

  2. 2. Complete the code

    Whose book is due today, January 15, 2026? With a 3-day loan period, it's the one that went out 3 days ago. Fill in the blank with the date change.

  3. 3. Predict the output

    Loan 1 went out on January 2, 2026, and came back on the 9th. Loan 8 went out on December 20, 2025, and came back on January 4, 2026. Someone subtracts the two columns without JULIANDAY. What does this query return?

  4. 4. Find the bug

    How many days has each book that hasn't come back been out? Today is January 15, 2026. The query returns the three members with the days column empty. Which line has the problem?

  5. 5. Put the lines in order

    Which of the books still out are already overdue? A book due today, January 15, 2026, can still be returned today, and it doesn't count. Put the query in order. Two lines don't belong.

  6. 6. Find the case that fails

    Someone worked out how long each book took to come back by subtracting the days of the month, the return day minus the day it went out. With Ann's and Luke's four returned loans, as they are in the database, it's right. Which of these corrections breaks it?

  7. 7. Put the lines in order

    The store writes to whoever has gone longest without coming back. Put in order the query that gives, for each customer who has ordered something, the days from their latest order to today, January 15, 2026. Whoever has gone longest comes first, and ties go by customer number. Order 12, still carrying its mistyped date of the 20th, is left out, and Luke's latest purchase with it. Two lines don't belong.

  8. 8. Complete the code

    The owner asks only about the past week, January 9 to 15. Fill in the blanks in the step that makes those seven days.

  9. 9. Write the code

    Last unit, the summary by day jumped from January 4 to the 9th without a word. It was missing the calendar, and now you have it. The step is already written. Below it, write the query that gives each day and how many orders came in that day, with a 0 where there were none. Two columns, the day and then the count, with the most recent day on top. Order 12, with its date of the 20th, finds no day in this calendar and isn't counted. Give the step the alias c and orders the alias o.

Do this lesson

It opens in your browser. This lesson is part of the full plan; the first unit of each course is free.

See all lessons →