How to Order by Month Name in SQLite

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.

You want to sort the rows by birthday_month.

Solution:

SELECT *
FROM birthday
ORDER BY
 (CASE birthday_month
    WHEN 'January' THEN 1
    WHEN 'February' THEN 2
    WHEN 'March' THEN 3
    WHEN 'April' THEN 4
    WHEN 'May' THEN 5
    WHEN 'June' THEN 6
    WHEN 'July' THEN 7
    WHEN 'August' THEN 8
    WHEN 'September' THEN 9
    WHEN 'October' THEN 10
    WHEN 'November' THEN 11
    WHEN 'December' THEN 12
  END);

The result looks like this (the rows are sorted in ascending order by birthday_month):

Discussion:

To sort the rows by month, you need the months in numbers (not names). You can convert month names to numerical months using the CASE WHEN clause. After the CASE keyword, specify the name of the column. Then, after each WHEN, state the value in this column, use the THEN keyword, and specify the new value you'd like to assign instead of the old one. Here, the column is birthday_month, the current values in this column are 'January', 'February', ..., 'December', and the new values are the numerical months 1, 2, ..., 12. After you finish converting all the values, remember to use the END keyword to close the CASE WHEN clause. Take a look:

CASE birthday_month
  WHEN 'January' THEN 1
  WHEN 'February' THEN 2
  WHEN 'March' THEN 3
  WHEN 'April' THEN 4
  WHEN 'May' THEN 5
  WHEN 'June' THEN 6
  WHEN 'July' THEN 7
  WHEN 'August' THEN 8
  WHEN 'September' THEN 9
  WHEN 'October' THEN 10
  WHEN 'November' THEN 11
  WHEN 'December' THEN 12
END

This is how you convert a month name to a month number. You can use it to sort the rows in ascending order by month – just use it in the ORDER BY clause.

Note that in SQLite, 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 second and Narelle Dillan third, or Narelle Dillan second and Angie Julia third). To change the order to descending, use the DESC keyword after the column in the ORDER BY clause. Here's what the query should look like:

SELECT *
FROM birthday
ORDER BY
  (CASE birthday_month
    WHEN 'January' THEN 1
    WHEN 'February' THEN 2
    WHEN 'March' THEN 3
    WHEN 'April' THEN 4
    WHEN 'May' THEN 5
    WHEN 'June' THEN 6
    WHEN 'July' THEN 7
    WHEN 'August' THEN 8
    WHEN 'September' THEN 9
    WHEN 'October' THEN 10
    WHEN 'November' THEN 11
    WHEN 'December' THEN 12
  END) DESC;