Back to cookbooks list Articles Cookbook

How to Order by Month Name in MySQL

Problem:

You want to sort the rows by month number, given month names (you want January to be shown first, December last).

Example:

The birthday table contains two columns: name and birthday_month. The months are given in names, not in numbers.p>

namebirthday_month
Ronan TishaNULL
December2020
Angie JuliaApril
Narelle DillanApril
Purdie CaseyJanuary
Donna NellNULL
Blaze GraemeOctober

You want to sort the rows by birthday_month.

Solution:

SELECT *
FROM birthday
ORDER BY STR_TO_DATE(CONCAT('0001 ', birthday_month, ' 01'), '%Y %M %d');

The result looks like this:

namebirthday_month
Donna NellNULL
Purdie CaseyJanuary
Angie JuliaApril
Narelle DillanApril
Blaze GraemeOctober
Ronan TishaNULL
DecemberOctober

Discussion:

To order by month, create a date with this month. To do this, use the STR_TO_DATE() function. If you have a date stored as a string in the 'Year Month Day' format, you can cast it to a date using STR_TO_DATE(date_string, '%Y %M %d').

First, you need to create a string using the CONCAT() function. The year can be any year you want (e.g., 0001) and the day can be any day you want (e.g., 01):

CONCAT('0001 ', birthday_month, ' 01')

The CONCAT() function combines all the arguments into one string. The string should be in the 'Year Month Day' format, so the second argument should be birthday_month; the first and the third arguments just need to be in the correct formats.

Then, you need to convert this string to a date using the STR_TO_DATE(date_string, '%Y %M %d') function. The second argument of this function is the format of the date. %Y stands for year, %M stands for month (its full name, not a number), and %d stands for day.

STR_TO_DATE(CONCAT('0001 ', birthday_month, ' 01'), '%Y %M %d')

Use it with an ORDER BY clause to sort the rows in ascending order by date. If you'd like to see the rows in descending order, just append a DESC keyword, like this:

SELECT *
FROM birthday
ORDER BY STR_TO_DATE(CONCAT('0001 ', birthday_month, ' 01'), '%Y %M %d') DESC;

Note that in MySQL, NULLs are displayed first when sorting in ascending order and last when sorting in descending order. Also, the rows with the same birthday_month are displayed in random order (you may see Angie Julia third and Narelle Dillan fourth, or Narelle Dillan third and Angie Julia fourth).

Recommended courses:

Recommended articles:

See also: