SQL julianday()
Count the days between two dates in SQLite.
Syntax
julianday(date2) - julianday(date1)
julianday turns a date into a day number, so subtracting two gives the gap in days.
Example
Query (on the shop database used in the SQL course)
SELECT order_id, order_date, CAST(julianday('2025-12-31') - julianday(order_date) AS INTEGER) AS days_to_year_end FROM orders LIMIT 4;
Result
| order_id | order_date | days_to_year_end |
|---|---|---|
| 1 | 2025-06-10 | 204 |
| 2 | 2025-05-17 | 228 |
| 3 | 2025-03-25 | 281 |
| 4 | 2025-10-26 | 66 |
4 rows
Good to know. PostgreSQL: subtract dates directly (date2 - date1).
Related
Learn it step by step in the free lesson Functions: Text, Numbers, Dates & CASE, with exercises that check your queries.