Sort by month, then by day within month

     
mysql>
mysql> CREATE TABLE event
    -> (
    ->  date            DATE,
    ->  description     VARCHAR(255)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO event (date,description)
    ->  VALUES
    ->          ('1789-07-04','US Independence Day'),
    ->          ('1776-07-14','Bastille Day'),
    ->          ('1957-10-04','Sputnik launch date'),
    ->          ('1958-01-31','Explorer 1 launch date'),
    ->          ('1919-06-28','Signing of the Treaty of Versailles'),
    ->          ('1732-02-22','George Washington\'s birthday'),
    ->          ('1989-11-09','Opening of the Berlin Wall'),
    ->          ('1944-06-06','D-Day at Normandy Beaches'),
    ->          ('1215-06-15','Signing of the Magna Carta'),
    ->          ('1809-02-12','database Lincoln\'s birthday');
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

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

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

mysql>
mysql> drop table event;
Query OK, 0 rows affected (0.00 sec)

Related examples in the same category

1.	Have birthdays next month
2.	Birthdays in the upcoming month
3.	Get month of a date
4.	Use MONTH to get the month of a date
5.	Use MONTHNAME in where clause
6.	Use MONTH to get the month of a date 2
7.	Obtaining the Current Year, Month, Day, Hour, Minute, or Second
8.	Extract only the month by using the MONTH() function: MONTH()
9.	To ensure that the month has two digits-as required for ISO format-use LPAD( ) to add a leading zero as necess

Sort by month, then by day within month : Month « Date Time « SQL / MySQL

Related examples in the same category