Skip to the content
Start

Parts of a date

SQL · Unit 14: Text and dates

Back to the school. Each month it wishes a happy birthday to whoever has one. Who has one in January? The answer is inside the born column, which you already know, between its two hyphens.

STRFTIME pulls parts out of a date. First comes a format and then the date. In the format, %Y is the year, %m is the month, and %d is the day, and case matters.

What it returns is text, 01 and not 1.

SELECT name, born,
  STRFTIME('%m', born) AS month
FROM students
WHERE year >= 2
ORDER BY month;

Returns

namebornmonth
Julia2011-01-3001
Leo2010-04-1704
Henry2011-06-2106
Megan2010-09-0909
Sara2010-12-2512

The rest of the explanation is in the lesson, which is part of the full plan.

Exercises in this lesson

You do them in the app, which checks them on the spot and explains why.

  1. 1. Predict the output

    Lucas and Emma, both in first year, were born on March 14 and November 2, 2012. What does this query return?

  2. 2. Complete the code

    The question the lesson started with. Who has a birthday in January? Fill in the format and the month.

  3. 3. Find the bug

    The third-year list was supposed to show the month each student was born in. Leo is from April, Megan from September, and Sara from December. All three show 00. Which line has the problem?

  4. 4. Put the lines in order

    The school hangs the birthday calendar in the hallway, January to December, without looking at what year anyone was born. The format '%m-%d' gives the month and the day, with a hyphen in between. Put the query in order. Two lines don't belong.

  5. 5. Multiple choice

    Sara, a third-year student, was born on December 25, 2010. Today, January 15, 2026, what does subtracting the years give for her, and how old is she?

  6. 6. Complete the code

    The interschool tournament sets its divisions by how old each student will be on June 30, 2026. Fill in the blanks for Lucas and Emma, both in first year, who were born on March 14 and November 2, 2012.

  7. 7. Find the case that fails

    Ages as of today, January 15, 2026, for the second-year students. Someone who has a birthday today is already a year older. This query gets Henry and Julia right. Which of these changes makes it give an age that's wrong?

  8. 8. Multiple choice

    You copy the January birthdays query onto a MySQL server, and it fails with a message saying the function STRFTIME doesn't exist. What do you do?

  9. 9. Predict the output

    Someone shortens the format and groups by the month alone. The data is the same: one loan from December 2025 and seven from January 2026. What does this query return?

  10. 10. Put the lines in order

    Of the loans that have already come back, how many had gone out each month? Put the query in order, with the most recent month first. Two lines don't belong.

  11. 11. Write the code

    The librarian closes her books month by month, and the loans that went out one month and came back another get in her way. Write a query with four columns, in this order: id, member, borrowed, and returned, for those loans. Compare the year and the month together. A book that went out in January 2025 and came back in January 2026 has to show up too.

Do this lesson

It opens in your browser. This lesson is part of the full plan; the first unit of each course is free.

See all lessons →