MySQL Cookbook

Sorting by Calendar Day

Problem

You want to sort by day of the calendar year.

Solution

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

Discussion

Sorting in calendar order differs from sorting by date. You ignore the year part of the dates and sort using only the month and day to order records in terms of where they fall during the calendar year. Suppose 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, 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 birthday | ...