Years and Months
Strftime.
Helen asks, how many orders did we get in twenty twenty four? To answer that, we need the year part of each date. SQLite has a function for this with a funny name, strftime. It stands for string format time.

- Orders in 2024?
- We need the year part
- Meet strftime
Pull out parts of a date
Strftime takes a pattern, and a date. Percent capital Y means the four digit year. Percent small m means the two digit month. Run it. Each order now shows its year and month in separate columns.
SELECT order_id,
order_date,
strftime('%Y', order_date) AS year,
strftime('%m', order_date) AS month
FROM orders
LIMIT 3;โโโโโโโโโโโโฌโโโโโโโโโโโโโฌโโโโโโโฌโโโโโโโโ โ order_id โ order_date โ year โ month โ โโโโโโโโโโโโผโโโโโโโโโโโโโผโโโโโโโผโโโโโโโโค โ 1001 โ 2025-03-06 โ 2025 โ 03 โ โ 1002 โ 2024-11-24 โ 2024 โ 11 โ โ 1003 โ 2024-06-22 โ 2024 โ 06 โ โโโโโโโโโโโโดโโโโโโโโโโโโโดโโโโโโโดโโโโโโโโ
Orders in one year
Now we can filter on the year. Where strftime of the year equals twenty twenty four, in quotes, because strftime gives back text. Run it. Five hundred and fifty nine orders in twenty twenty four. Helen's shop is growing, because twenty twenty five had almost twice as many.
SELECT COUNT(*)
FROM orders
WHERE strftime('%Y', order_date) = '2024';โโโโโโโโโโโโ โ COUNT(*) โ โโโโโโโโโโโโค โ 559 โ โโโโโโโโโโโโ
Handy patterns
Here are the patterns you'll use most. Percent capital Y for the year. Percent small m for the month. Percent small d for the day. And percent small w for the day of the week, where zero is Sunday. Other databases use different function names, like year and month, but the idea is the same.
| key | value |
|---|---|
| %Y | year: 2025 |
| %m | month: 01-12 |
| %d | day: 01-31 |
| %w | weekday: 0 = Sunday |
Summary
Strftime pulls out the year, month, or day of a date. Next, doing maths with dates.
Key takeaways- strftime('%Y', date)
- It returns text
- Compare with quotes