Days between two dates in SQL (and the text date trap)

Why the usual answer is wrong

SELECT user_id, first_order_date - signup_date AS days
FROM users;

SQLite stores these dates as text. Text minus text turns each date into its leading number, the year, so every row shows 2026 - 2026 = 0.

The answer

SELECT user_id,
  CAST(julianday(first_order_date) - julianday(signup_date) AS INTEGER) AS days
FROM users;

julianday turns a date into a day number, so subtracting two of them gives the days between. CAST keeps it a whole number.

In PostgreSQL, date - date works directly; in MySQL use DATEDIFF(b, a); in SQL Server DATEDIFF(day, a, b). Mention you'd check the column type first.