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

Updated on 30-Jul-2019 22:30:25

• Related Questions & Answers
• MySQL query to select rows older than a week?
• How can I get the current week using Python?
• MySQL query to get current datetime and only current date
• C# Program to get current day of week
• Java Program to subtract week from current date
• MySQL query to get the current date records wherein one of the columns displays current date
• MySQL query to get the current date from the list of dates
• Filter query by current date in MySQL
• MySQL query to insert current date plus specific time?
• MySQL query to order by current day and month?
• Get the week number in MySQL for day of week?
• MySQL query to subtract date records with week day and display the weekday with records
• How to validate expiry date on a MySQL query?
• Java Program to get date for all the days of the current week
• MySQL query to select date >= current date - 3 weeks?