How to Order by Month Name in PostgreSQL or Oracle

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 EXTRACT(MONTH FROM TO_DATE(birthday_month, 'Month'));

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

Discussion:

To sort the rows by month starting from January and ending with December, you need to convert the month to a number ('January' to 1, 'February' to 2, etc.). Otherwise, you would see 'December' before 'January'. The TO_DATE(birthday_month, 'Month') function converts a full month name to a date in the '0001-MM-01' format. For example, you get '0001-12-01' for December.

You can now use the EXTRACT(MONTH FROM date) function to extract the month from this date value. The month will be a number between 1 and 12.

Combining these two functions, you can get the month as a number using the following formula:

EXTRACT(MONTH FROM TO_DATE(birthday_month, 'Month'))

Put this expression after ORDER BY to sort the rows by month. If you'd like to see the latest month first, you'll need to sort in descending order. To do this, you need to use a DESC keyword, like this:

SELECT *
FROM birthday
ORDER BY EXTRACT(MONTH FROM TO_DATE(birthday_month, 'Month')) DESC;

Note that in PostgreSQL and Oracle, NULLs are displayed last when sorting in ascending order and first 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).