Days Between Dates

How many days are there between two dates? Subtracting them doesn't work, and it doesn't even give an error:

SELECT '2024-01-20' - '2023-01-15' AS days;

Dates are texts, and when SQLite has to calculate with a text, it takes the number at its start and ignores the rest. So this is 2024 - 2023, which is 1. Dates first have to become numbers, and that's what julianday(date) does: it gives the number of days since a day long ago (November 24, 4714 BC, which astronomers count as day 0). The number itself isn't interesting, but the difference of two of them is the number of days between the dates:

SELECT julianday('2024-01-20') AS first_order,
       julianday('2023-01-15') AS joined,
       julianday('2024-01-20') - julianday('2023-01-15') AS days;

Emma Clark placed her first order 370 days after she joined. (The .5 is there because, for astronomers, a day starts at noon; in a difference, it cancels out.)

The difference is a decimal number, 370.0, because julianday counts hours too: julianday('2024-01-20 18:00') is three quarters of a day more. To get a whole number, convert it with CAST(x AS INTEGER). CAST converts a value to another type: INTEGER, REAL or TEXT. To a whole number, it cuts off the decimal part, as whole division does: CAST(3.9 AS INTEGER) is 3. Dates without times are always whole days apart, so nothing is lost. How many days had each customer been with the shop on New Year's Day 2025?

SELECT name, joined,
       CAST(julianday('2025-01-01') - julianday(joined) AS INTEGER) AS days
FROM customers
LIMIT 3;

You could use ROUND instead of CAST, but that gives 717.0. And once the days are a whole number, whole division gives whole weeks: 717 / 7 is 102.

For NULL, julianday gives NULL, as other functions do, so a game that isn't out yet has no number of days since its release. And julianday('now') is right now, so julianday('now') - julianday(joined) is how long a customer has been with the shop today, a number that grows every day.

Typical mistake: the dates the other way around. julianday(joined) - julianday('2025-01-01') gives -717.0 for Emma Clark. Always subtract the earlier date from the later one.