How to query MySQL on the current week?

To query MySQL on the current week, you can use YEARWEEK() function.

The syntax is as follows

SELECT *FROM yourTableName WHERE YEARWEEK(yourDateColumnName) = YEARWEEK(NOW());

To understand the above syntax, let us create a table. The query to create a table is as follows

mysql> create table currentWeekDemo
   -> (
   -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> UserName varchar(20),
   -> UserPostDate date
   -> );
Query OK, 0 rows affected (0.68 sec)

Insert some records in the table using insert command. The query is as follows −

mysql> insert into currentWeekDemo(UserName,UserPostDate) values('Carol','2010-01-06');
Query OK, 1 row affected (0.20 sec)
mysql> insert into currentWeekDemo(UserName,UserPostDate) values('Bob','2019-06-04');
Query OK, 1 row affected (0.17 sec)
mysql> insert into currentWeekDemo(UserName,UserPostDate) values('David','2019-03-06');
Query OK, 1 row affected (0.16 sec)
mysql> insert into currentWeekDemo(UserName,UserPostDate) values('Maxwell','2018-04-21');
Query OK, 1 row affected (0.15 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from currentWeekDemo;

The following is the output

+--------+----------+--------------+
| UserId | UserName | UserPostDate |
+--------+----------+--------------+
|      1 | Carol    | 2010-01-06   |
|      2 | Bob      | 2019-06-04   |
|      3 | David    | 2019-03-06   |
|      4 | Maxwell  | 2018-04-21   |
+--------+----------+--------------+
4 rows in set (0.00 sec)

The following is how you can query MySQL on the current week

mysql> select *from currentWeekDemo WHERE YEARWEEK(UserPostDate) = YEARWEEK(NOW());

The following is the output

+--------+----------+--------------+
| UserId | UserName | UserPostDate |
+--------+----------+--------------+
|      3 | David    | 2019-03-06   |
+--------+----------+--------------+
1 row in set (0.05 sec)

George John

e

Updated on: 30-Jul-2019

2K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started