The following statement is trying to retrieve current week's data and will wrongly select extra data when using WEEK():
mysql> SELECT id, create_date, create_time FROM table_name WHERE WEEK(create_time) = WEEK(CURRENT_DATE);
+----+-------------+---------------------+
| id | create_date | create_time |
+----+-------------+---------------------+
| 13 | 2013-07-09 | 2013-07-09 20:54:47 |
| 14 | 2013-07-09 | 2013-07-09 20:54:48 |
| 15 | 2013-07-09 | 2013-07-09 20:54:49 |
| 16 | 2013-07-09 | 2013-07-09 20:54:50 |
| 17 | 2013-07-09 | 2013-07-09 20:54:51 |
| 18 | 2014-07-10 | 2014-07-09 20:54:53 |
+----+-------------+---------------------+
6 rows in set (0.01 sec)
An extra row created in 2014 is wrongly selected in such statement. The correct way to do it is to use YEARWEEK() instead:
mysql> SELECT YEARWEEK(CURRENT_DATE);
+------------------------+
| YEARWEEK(CURRENT_DATE) |
+------------------------+
| 201327 |
+------------------------+
1 row in set (0.00 sec)
Since YEARWEEK() returns information containing year and month. So we can rewrite the statement into as following:
mysql> SELECT id, create_date, create_time FROM table_name WHERE YEARWEEK(create_time) = YEARWEEK(CURRENT_DATE);
+----+-------------+---------------------+
| id | create_date | create_time |
+----+-------------+---------------------+
| 13 | 2013-07-09 | 2013-07-09 20:54:47 |
| 14 | 2013-07-09 | 2013-07-09 20:54:48 |
| 15 | 2013-07-09 | 2013-07-09 20:54:49 |
| 16 | 2013-07-09 | 2013-07-09 20:54:50 |
| 17 | 2013-07-09 | 2013-07-09 20:54:51 |
+----+-------------+---------------------+
5 rows in set (0.00 sec)