Treat Monday as the first day of the week and Sunday as the last, use a the MOD( ) function to map Monday to 0

     

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)

Related examples in the same category

1.	MOD in MySQL
2.	Use the MOD() function to divide the Amount values by 10 and then return the remainder from that division.
3.	The MOD() function returns the remainder derived by dividing two numbers.
4.	modulus operator

Treat Monday as the first day of the week and Sunday as the last, use a the MOD( ) function to map Monday to 0 : MOD « Math « SQL / MySQL

Related examples in the same category