SQL Tutorial · Aggregation and Functions · Lesson 17 of 36
SQL Date Functions
Extract parts of dates, add intervals and group by month.
Dates in SQLite
SQLite stores dates as ISO text and has functions date(), datetime() and strftime():
SELECT order_id, order_date,
strftime('%Y', order_date) AS year,
strftime('%m', order_date) AS month,
date(order_date, '+14 days') AS due_date,
julianday('2026-07-01') - julianday(order_date) AS days_before_july
FROM orders WHERE order_id <= 3;
Output
order_id | order_date | year | month | due_date | days_before_july 1 | 2026-01-05 | 2026 | 01 | 2026-01-19 | 177 2 | 2026-01-12 | 2026 | 01 | 2026-01-26 | 170 3 | 2026-02-02 | 2026 | 02 | 2026-02-16 | 149
Group by month
SELECT strftime('%Y-%m', o.order_date) AS month,
COUNT(DISTINCT o.order_id) AS orders,
ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue
FROM orders o JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status <> 'cancelled'
GROUP BY month
ORDER BY month;
Output
month | orders | revenue 2026-01 | 2 | 167.98 2026-02 | 3 | 561.38 2026-03 | 1 | 114.99 2026-04 | 2 | 747 2026-05 | 2 | 131.45 2026-06 | 1 | 102
Other databases
| Task | SQLite | PostgreSQL | MySQL |
|---|---|---|---|
| today | date('now') |
CURRENT_DATE |
CURDATE() |
| add 7 days | date(d, '+7 days') |
d + INTERVAL '7 days' |
DATE_ADD(d, INTERVAL 7 DAY) |
| month of a date | strftime('%m', d) |
EXTRACT(MONTH FROM d) |
MONTH(d) |
| truncate to month | strftime('%Y-%m', d) |
date_trunc('month', d) |
DATE_FORMAT(d, '%Y-%m') |
Test yourself
In SQLite, which function formats a date with a pattern like '%Y-%m'?
date_format()strftime()to_char()format()
Show answer
Answer: strftime()