How to Select Current Month's Data in MySQL (3/3)

Similarly, use MONTH() to retrieve current month's data will get wrong results.
mysql> SELECT id, create_date, create_time FROM table_name WHERE MONTH(create_time) = MONTH(CURRENT_DATE);
+----+-------------+---------------------+
| id | create_date | create_time         |
+----+-------------+---------------------+
|  8 | 2012-07-09  | 2012-07-09 19:55:01 |
|  9 | 2012-07-09  | 2012-07-09 19:55:08 |
| 10 | 2012-07-09  | 2012-07-09 19:55:13 |
| 11 | 2012-07-09  | 2012-07-09 19:55:20 |
| 12 | 2012-07-09  | 2012-07-09 19:55:26 |
| 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 |
+----+-------------+---------------------+
11 rows in set (0.00 sec)

The correct way is to add a condition in where clause to filter out the data with wrong years.

mysql> SELECT id, create_date, create_time FROM table_name WHERE YEAR(create_time) = YEAR(CURRENT_DATE) AND MONTH(create_time) = MONTH(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)

As we mentioned in previous articles, although DATE_FORMAT() is more consuming, it's very flexible to design retrieving logic in various operations.

mysql> SELECT id, create_date, create_time FROM table_name WHERE DATE_FORMAT(create_time,'%Y%m') = DATE_FORMAT(CURRENT_DATE,'%Y%m');
+----+-------------+---------------------+
| 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)