AI Hints
Homeโ€บSQL Tutorialโ€บWorking with Dates
SQL ยท Lesson 28 of 126

Years and Months

Strftime.

โฑ 2 min readโ–ถ Video at 39:13๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

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.

Years and Months
From the video: Years and Months
  • 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.

query.sqlsql
SELECT order_id,
       order_date,
       strftime('%Y', order_date) AS year,
       strftime('%m', order_date) AS month
FROM orders
LIMIT 3;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ 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.

query.sqlsql
SELECT COUNT(*)
FROM orders
WHERE strftime('%Y', order_date) = '2024';
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ 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.

keyvalue
%Yyear: 2025
%mmonth: 01-12
%dday: 01-31
%wweekday: 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
Next up: Date Maths โ€” adding days, counting days