Crear cuenta
mysql> SELECT * FROM dias;
+------------+
| dia |
+------------+
| 2016-11-01 |
| 2016-11-02 |
| 2016-11-03 |
| 2016-11-04 |
| 2016-11-05 |
| 2016-11-06 |
| 2016-11-07 |
| 2016-11-08 |
| 2016-11-09 |
| 2016-11-10 |
+------------+
10 rows in set (0.00 sec)
mysql> SELECT-> dia,
-> DAYOFWEEK(dia) normal,
-> CASE DAYOFWEEK(dia)
-> WHEN 1 THEN 2
-> WHEN 2 THEN 3
-> WHEN 3 THEN 4
-> WHEN 4 THEN 5
-> WHEN 5 THEN 6
-> WHEN 6 THEN 7
-> WHEN 7 THEN 1
-> END modificado
-> FROM dias;
+------------+--------+------------+
| dia | normal | modificado |
+------------+--------+------------+
| 2016-11-01 | 3 | 4 |
| 2016-11-02 | 4 | 5 |
| 2016-11-03 | 5 | 6 |
| 2016-11-04 | 6 | 7 |
| 2016-11-05 | 7 | 1 |
| 2016-11-06 | 1 | 2 |
| 2016-11-07 | 2 | 3 |
| 2016-11-08 | 3 | 4 |
| 2016-11-09 | 4 | 5 |
| 2016-11-10 | 5 | 6 |
+------------+--------+------------+
10 rows in set (0.00 sec)
mysql> SELECT-> dia,
-> WEEK(ADDDATE(dia, INTERVAL 1 DAY)) week_siguiente_dia
-> FROM dias;
+------------+--------------------+
| dia | week_siguiente_dia |
+------------+--------------------+
| 2017-11-01 | 44 |
| 2017-11-02 | 44 |
| 2017-11-03 | 44 |
| 2017-11-04 | 45 |
| 2017-11-05 | 45 |
| 2017-11-06 | 45 |
| 2017-11-07 | 45 |
| 2017-11-08 | 45 |
| 2017-11-09 | 45 |
| 2017-11-10 | 45 |
| 2017-11-11 | 46 |
| 2017-11-12 | 46 |
+------------+--------------------+
12 rows in set (0.00 sec)
mysql> SELECT-> dia,
-> WEEK(ADDDATE(dia, INTERVAL 1 DAY)) week_siguiente_dia
-> FROM dias
-> WHERE
-> WEEK(ADDDATE(dia, INTERVAL 1 DAY)) = 45;
+------------+--------------------+
| dia | week_siguiente_dia |
+------------+--------------------+
| 2017-11-04 | 45 |
| 2017-11-05 | 45 |
| 2017-11-06 | 45 |
| 2017-11-07 | 45 |
| 2017-11-08 | 45 |
| 2017-11-09 | 45 |
| 2017-11-10 | 45 |
+------------+--------------------+
7 rows in set (0.00 sec)