Dates and Times in SQLite
A library due-date stamp does math for you: today plus 14 days. And every morning, someone checks which stamps are already in the past.
Dates are everywhere in the library database: when a member joined, when a book was lent, when it's due, when it came back. So far, you've compared them with < and >, which works because they're written as 'YYYY-MM-DD' text. But a real library needs to do math with dates: when is a book due if it's lent today? How many days late is it? Which loans are from this month?
SQLite has a small set of date functions for exactly that.
A due-date stamp
At a library desk, the librarian has a stamp that's set to "today plus 14 days." Every book that goes out gets stamped with its due date. And every morning, someone looks through the loans and asks: which of these due dates is before today?
Those are the two jobs you'll do most with dates: calculate a new date from an old one, and compare or measure the gap between two dates.
How SQLite stores dates
As you learned in Creating Tables, SQLite has no special date type. Dates are stored as text in the international standard format, called ISO 8601:
- a date:
'2026-09-29' - a date and time:
'2026-09-29 14:30:00'
Always use this format, with the year first and two-digit months and days. It's what SQLite's date functions understand, and because the biggest part comes first, dates written this way sort and compare correctly as plain text. '2026-10-04' > '2026-09-29' is true, just as it should be. Write dates as '29/09/2026', and none of that works.
Today's date
date('now') gives today's date, and datetime('now') gives the current date and time. (They use UTC, the world's standard time, not your local time; add 'localtime', like date('now', 'localtime'), for yours.)
Because "now" changes every second, the examples in this lesson use a fixed day, '2026-09-29', so the answers are always the same. In a real app, you'd write date('now') in its place.
Adding and subtracting: modifiers
date() can take a starting date and one or more modifiers, which move it:
SELECT
date('2026-09-29', '+14 days') AS due,
date('2026-09-29', '-1 month') AS month_ago,
date('2026-09-29', '+1 year') AS next_year;+------------+------------+------------+
| due | month_ago | next_year |
+------------+------------+------------+
| 2026-10-13 | 2026-08-29 | 2027-09-29 |
+------------+------------+------------+There are special modifiers too:
SELECT
date('2026-09-29', 'start of month') AS first_of_month,
date('2026-09-29', 'start of month', '+1 month', '-1 day') AS last_of_month;+----------------+---------------+
| first_of_month | last_of_month |
+----------------+---------------+
| 2026-09-01 | 2026-09-30 |
+----------------+---------------+The second one chains three modifiers, applied in order: go to the first of this month, move forward a month, then back one day. That's a common trick for "the last day of this month."
The library's due-date stamp, as an INSERT:
INSERT INTO loans (book_id, member_id, loan_date, due_date)
VALUES (5, 5, '2026-09-29', date('2026-09-29', '+14 days'));
SELECT id, loan_date, due_date FROM loans WHERE member_id = 5;+----+------------+------------+
| id | loan_date | due_date |
+----+------------+------------+
| 8 | 2026-09-29 | 2026-10-13 |
+----+------------+------------+Days between two dates: julianday()
To count the days between two dates, turn each into a day number with julianday() and subtract. (The Julian day is a count of days since a date thousands of years ago, which makes subtraction easy.)
SELECT julianday('2026-10-04') - julianday('2026-09-29') AS days;+------+
| days |
+------+
| 5.0 |
+------+It gives a decimal, since dates can include times. Wrap it in CAST(... AS INTEGER) for a whole number of days. Here's the library's "overdue" report, as of our fixed day:
SELECT
m.name,
b.title,
l.due_date,
CAST(julianday('2026-09-29') - julianday(l.due_date) AS INTEGER) AS days_late
FROM loans l
JOIN members m ON l.member_id = m.id
JOIN books b ON l.book_id = b.id
WHERE l.returned_date IS NULL AND l.due_date < '2026-09-29'
ORDER BY days_late DESC;+-------------+-------------------------+------------+-----------+
| name | title | due_date | days_late |
+-------------+-------------------------+------------+-----------+
| Carlo Reyes | A Brief History of Time | 2026-09-24 | 5 |
| Ben Cruz | Noli Me Tangere | 2026-09-26 | 3 |
+-------------+-------------------------+------------+-----------+Carlo's book was due on the 24th and Ben's on the 26th. The other two open loans aren't due yet, so the WHERE left them out.
Pulling dates apart: strftime()
strftime(format, date) formats a date however you like, using codes that start with %:
| Code | Means | For '2026-09-29' |
|---|---|---|
%Y | four-digit year | 2026 |
%m | month, 01 to 12 | 09 |
%d | day of the month | 29 |
%w | day of the week, 0 is Sunday | 2 |
%j | day of the year | 272 |
SELECT
strftime('%d/%m/%Y', '2026-09-29') AS local_style,
strftime('%Y', '2026-09-29') AS year,
strftime('%w', '2026-09-29') AS weekday;+-------------+------+---------+
| local_style | year | weekday |
+-------------+------+---------+
| 29/09/2026 | 2026 | 2 |
+-------------+------+---------+strftime gives back text, so %Y is the text '2026', not a number. It's most useful for grouping: how many loans were made in each month?
SELECT strftime('%Y-%m', loan_date) AS month, COUNT(*) AS loans
FROM loans
GROUP BY month
ORDER BY month;+---------+-------+
| month | loans |
+---------+-------+
| 2026-08 | 1 |
| 2026-09 | 6 |
+---------+-------+Date functions in other databases
Dates are one of the places where every database does things differently. The ideas are the same everywhere, but the function names aren't:
- MySQL has real
DATEandDATETIMEtypes, and functions likeDATE_ADD(d, INTERVAL 14 DAY)andDATEDIFF(a, b). - Oracle has a
DATEtype that always includes a time, and you can add days with plain+:due_date + 14.
The MySQL and Oracle tracks each cover their own versions.
Try it
A class is tracking library books for a reading challenge. As of the fixed day '2026-09-29', predict each result.
Running SQL here
Running SQL right in the browser is coming to Syntaxia soon. For now, you can read and edit the statements here. To run them yourself, open the shell with sqlite3, type .mode table, and paste them in.
Check your prediction
+---------+------------+------------+
| student | loan_date | due |
+---------+------------+------------+
| Maria | 2026-09-08 | 2026-09-22 |
| Ben | 2026-09-20 | 2026-10-04 |
| Carlo | 2026-08-30 | 2026-09-13 |
+---------+------------+------------+
+---------+-----------+
| student | days_late |
+---------+-----------+
| Maria | 7 |
| Carlo | 16 |
+---------+-----------+
+-------+-------+
| month | loans |
+-------+-------+
| 08 | 1 |
| 09 | 2 |
+-------+-------+Carlo's due date crosses into September, which date() handles for you. Ben's book is due on October 4, so it isn't overdue yet and Query 2 leaves him out. Query 3 groups by the two-digit month text, '08' and '09'.
Try it yourself
- In your library database, show each member's name and how many days they've been a member, as of
'2026-09-29'. - Find every loan that was due in September 2026, using
strftime. - Add a new loan for today using
date('now')for the loan date anddate('now', '+14 days')for the due date, then look at it. Is the date what you expected? (Remember that'now'uses UTC.)
Check your understanding
Up next
So far, you've typed every query into the shell yourself. Real apps run SQL from inside a program. Next, you'll do that from Python, which has SQLite built in, in Using SQLite from Python.