Mega Code Archive
Treat Monday as the first day of the week and Sunday as the last, use a the MOD( ) function to map Monday to 0
<![CDATA[
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 DAYNAME(date), date, description
-> FROM event
-> ORDER BY MOD(DAYOFWEEK(date) + 5, 7);
+---------------+------------+-------------------------------------+
| DAYNAME(date) | date | description |
+---------------+------------+-------------------------------------+
| Monday | 1215-06-15 | Signing of the Magna Carta |
| Tuesday | 1944-06-06 | D-Day at Normandy Beaches |
| Thursday | 1989-11-09 | Opening of the Berlin Wall |
| Friday | 1732-02-22 | George Washington's birthday |
| Friday | 1957-10-04 | Sputnik launch date |
| Friday | 1958-01-31 | Explorer 1 launch date |
| Saturday | 1789-07-04 | US Independence Day |
| Saturday | 1919-06-28 | Signing of the Treaty of Versailles |
| Sunday | 1776-07-14 | Bastille Day |
| Sunday | 1809-02-12 | database Lincoln's birthday |
+---------------+------------+-------------------------------------+
10 rows in set (0.00 sec)
mysql>
mysql> drop table event;
Query OK, 0 rows affected (0.00 sec)
]]>