Back to cookbooks list Articles Cookbook

How to Order by Date in MySQL

Problem:

You want to sort the rows by date.

Example 1:

The exam table has two columns, subject and exam_date.

subjectexam_date
Mathematics2019-12-19
English2020-01-08
Science2020-01-05
Health2020-01-05
ArtNULL

You want to sort the rows by exam_date.

Solution:

SELECT *
FROM exam
ORDER BY exam_date;

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

subjectexam_date
ArtNULL
Mathematics2019-12-19
Science2020-01-05
Health2020-01-05
English2020-01-08

Discussion:

Use the ORDER BY keyword and the name of the column by which you want to sort. This way, you'll sort the data in ascending order by this column. You could also use the ASC keyword to make it clear that the order is ascending (the earliest date is shown first, the latest date is shown last, etc.).

SELECT *
FROM exam
ORDER BY exam_date ASC;

If you'd like to see the latest date first and the earliest date last, you need to sort in descending order. Use the DESC keyword in this case.

SELECT *
FROM exam
ORDER BY exam_date 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 exam_date are displayed in random order (you may see Science third and Health fourth, or Health third and Science fourth).

Example 2:

The exam table has the following columns: subject, exam_year, exam_month, and exam_day. The months are given in names, not in numbers.

subjectexam_yearexam_monthexam_day
Mathematics2019December19
English2020January8
Science2020January5
Health2020January5
ArtNULLNULLNULL

You want to sort the rows by exam date.

Solution:

SELECT *
FROM exam
ORDER BY STR_TO_DATE(CONCAT(exam_year, ' ', exam_month, ' ', exam_day), '%Y %M %d');

The result looks like this (the rows are sorted in ascending order by exam_year, exam_month, and exam_date):

subjectexam_yearexam_monthexam_day
ArtNULLNULLNULL
Mathematics2019December19
Health2020January5
Science2020January5
English2020January8

Discussion:

To sort by date, create date values from the year, the month, and the day values. 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'). But first, you need to create a string using the CONCAT() function:

CONCAT(exam_year, ' ', exam_month, ' ', exam_day)

The CONCAT() function combines all the arguments into one string. You don't need to cast numbers to strings. Since you'd like to get a string in the 'Year Month Day' format, the arguments are exam_year, exam_month, exam_day, and the spaces between them.

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 date format. %Y stands for year, %M stands for month (its full name, not a number), and %d stands for day.

STR_TO_DATE(CONCAT(exam_year, ' ', exam_month, ' ', exam_day), '%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 exam
ORDER BY STR_TO_DATE(CONCAT(exam_year, ' ', exam_month, ' ', exam_day), '%Y %M %d') DESC;

Recommended courses:

Recommended articles:

See also: