SQL TOPIC

SQL dates: group by month and compare periods

Business questions are usually about time: revenue per month, signups this quarter, days to resolve.

In SQLite, dates are text like '2026-03-15'. strftime('%Y-%m', order_date) turns them into months for grouping.

julianday(end) − julianday(start) gives the number of days between two dates. Other databases use DATE_TRUNC or DATEDIFF, but the idea is the same.

Worked example

How many orders were placed each month?

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

Result on the sample data