9.2 How to Extract Year, Month and Day in SQLite with strftime()

By Ravindra Bagale Last updated: 31 October 2026

Why. Managers ask for totals by month or by year. You need the month as its own value to group by it.

What. strftime(format, date) returns a part of a date as text. '%Y' gives the year, '%m' gives the month as 01 to 12, '%d' gives the day, and '%Y-%m' gives year and month together, like 2026-09.

How. Split a few dates into parts, then count transactions per month with GROUP BY.

Steps

Run the first example and read the year, month and day columns.
Run the second example to get one row per month.
Check that the month column sorts correctly because it is in the YYYY-MM format.
Use the month column in your monthly report.

Examples

Example 1 · dataset: northstar_bank

SELECT posted_at,
       strftime('%Y', posted_at) AS year,
       strftime('%m', posted_at) AS month,
       strftime('%d', posted_at) AS day
FROM transactions
WHERE txn_id IN (2, 9, 18);

Output

posted_at	year	month	day
2026-08-19 14:40:00	2026	08	19
2026-09-14 13:10:00	2026	09	14
2026-10-20 16:45:00	2026	10	20

Meaning. Each date is split into year, month and day. The parts are text, so the month shows as 08, not 8.

Example 2 · dataset: northstar_bank

SELECT strftime('%Y-%m', posted_at) AS month,
       COUNT(*)                     AS transactions
FROM transactions
GROUP BY month
ORDER BY month;

Output

month	transactions
2026-08	7
2026-09	9
2026-10	9

Meaning. There is one row per month. August has 7 transactions, September has 9 and October has 9.

Ravindra Bagale's Tip

Use '%Y-%m' rather than '%m' alone for monthly reports. With '%m' alone, September of two different years would join into one group.