This is my table: mysql> desc testing; +-------+------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------+------+-----+---------+-------+ | date | date | YES | | NULL | | +-------+------+------+-----+---------+-------+
Here are the values:
mysql> select *from testing; +------------+ | date | +------------+ | 2004-04-10 | | 2004-04-15 | | 2004-01-01 | +------------+
Here is my question:
The following query returns incorrect rows and I dont understand why.
mysql> SELECT * FROM testing WHERE MONTH(date) = (MONTH(NOW()) OR MONTH(NOW())-1);
+------------+
| date |
+------------+
| 2004-01-01 |
+------------+
I wanted the query to return the rows whose months are from this month or last month.
As others have pointed out, this cannot work for January. (Not to mention that it's semantically not correct anyway.) You might want to adapt the technique shown at the bottom of this page:
http://dev.mysql.com/doc/mysql/en/Date_calculations.html
Note the use of the MOD operator to handle month number wraparound.
This query however, returns the correct rows:
mysql> SELECT * FROM testing WHERE MONTH(date) = MONTH(now()) OR MONTH(date) = MONTH(NOW())-1;
+------------+
| date |
+------------+
| 2004-04-10 |
| 2004-04-15 |
+------------+
Why does the first one not work? its shorter to type :)
Indeed, leaving out parts of the correct solution often will result in a shorter query. :-)
-- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
