Get the week number in MySQL for day of week?

MySQLMySQLi Database

MySQL DAYOFWEEK() function returns 1 for Sunday, 2 for Monday and so on for day of week. Let us see an example by first creating a table −

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

Inserting date in the table with the help of insert command. The query is as follows −

mysql> insert into DayOfWeekDemo values(date_add(curdate(),interval 5 day));
Query OK, 1 row affected (0.52 sec)

mysql> insert into DayOfWeekDemo values(date_add(curdate(),interval 6 day));
Query OK, 1 row affected (0.13 sec)

mysql> insert into DayOfWeekDemo values(date_add(curdate(),interval 7 day));
Query OK, 1 row affected (0.10 sec)

mysql> insert into DayOfWeekDemo values(date_add(curdate(),interval 8 day));
Query OK, 1 row affected (0.15 sec)

mysql> insert into DayOfWeekDemo values(date_add(curdate(),interval 9 day));
Query OK, 1 row affected (0.10 sec)

mysql> insert into DayOfWeekDemo values(date_add(curdate(),interval 10 day));
Query OK, 1 row affected (0.13 sec)

mysql> insert into DayOfWeekDemo values(date_add(curdate(),interval 11 day));
Query OK, 1 row affected (0.10 sec)

Now you can display how many records are present in the table with the help of select statement. The query is as follows −

mysql> select *from DayOfWeekDemo;

The following is the output −

+---------------------+
| 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)

Look at the table above: 2018-12-03 is a Monday, 2018-12-04 is Tuesday, etc. The following is the query to return the index of day of week −

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

The following is the output displaying day number of week −

+------------+
| WeekNumber |
+------------+
|          2 |
|          3 |
|          4 |
|          5 |
|          6 |
|          7 |
|          1 |
+------------+
7 rows in set (0.00 sec)
raja
Published on 10-Jan-2019 11:29:24
Advertisements