Lấy số thứ tự ngày trong tuần MySQL

DAYOFWEEK() trả 1=CN, 2=T2,... 7=T7.

Tạo bảng:

mysql> create table DayOfWeekDemo
-> (
-> Issuedate datetime
-> );
Query OK, 0 rows affected (0.52 sec)

Chèn 7 ngày liên tiếp:

mysql> insert into DayOfWeekDemo values(date_add(curdate(),interval 5 day));
... (lặp đến interval 11 day)

Hiển thị:

+---------------------+
| Issuedate           |
+---------------------+
| 2018-12-03 00:00:00 |
| 2018-12-04 00:00:00 |
| 2018-12-05 00:00:00 |
| 2018-12-06 00:00:00 |
| 2018-12-07 00:00:00 |
| 2018-12-08 00:00:00 |
| 2018-12-09 00:00:00 |
+---------------------+
7 rows in set (0.00 sec)

03/12 là T2, 04/12 T3,...

Truy vấn:

mysql> select dayofweek(Issuedate) as WeekNumber from DayOfWeekDemo;

Kết quả:

+------------+
| WeekNumber |
+------------+
| 2          |
| 3          |
| 4          |
| 5          |
| 6          |
| 7          |
| 1          |
+------------+
7 rows in set (0.00 sec)