How to Order by Month Name in MySQL

Problem:

In a MySQL database, 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.

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:

Discussion:

To order by month in MySQL, 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 with the date 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 arbitrary numbers.

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 full name of the month, and%dstands 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 fifth and Narelle Dillan fourth, or Narelle Dillan fifth and Angie Julia fourth).