MySQL Cookbook, 2nd Edition

Sorting by Calendar Day

Problem

You want to sort by day of the calendar year.

Solution

Sort using the month and day of date values, ignoring the year.

Discussion

Sorting in calendar order differs from sorting by date. You need to ignore the year part of the dates and sort using only the month and day to order rows in terms of where they fall during the calendar year. Suppose that you have an event table that looks like this when values are ordered by actual date of occurrence:

mysql>SELECT date, description FROM event ORDER BY date;
+------------+-------------------------------------+
| date       | description                         |
+------------+-------------------------------------+
| 1215-06-15 | Signing of the Magna Carta          |
| 1732-02-22 | George Washington's birthday        |
| 1776-07-14 | Bastille Day                        |
| 1789-07-04 | US Independence Day                 |
| 1809-02-12 | Abraham Lincoln's birthday          |
| 1919-06-28 | Signing of the Treaty of Versailles |
| 1944-06-06 | D-Day at Normandy Beaches           |
| 1957-10-04 | Sputnik launch date                 |
| 1958-01-31 | Explorer 1 launch date              |
| 1989-11-09 | Opening of the Berlin Wall          |
+------------+-------------------------------------+

To put these items in calendar order, sort them by month, and then by day within month:

mysql>SELECT date, description FROM event
    -> ORDER BY MONTH(date), DAYOFMONTH(date); +------------+-------------------------------------+ | date | description | +------------+-------------------------------------+ | 1958-01-31 | Explorer 1 launch date | | 1809-02-12 | Abraham Lincoln's ...