mayuran wrote:
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.
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 :)
Thanks
"Shorter to type" doesn't necessarily mean "does what you want." (MONTH(NOW()) OR MONTH(NOW())-1) evaluates to (5 OR 4). The BOOLEAN OR operator returns 1 if either opperand is TRUE (nonzero), or 0 if both operands are FALSE (0). Hence, (5 OR 4) = 1. So, your shorter query evaluates like this:
SELECT * FROM testing WHERE MONTH(date) = 1;
So you are getting the correct result.
Your second, longer query, or the alternatives sent by Victor and Matt, will do what you want.
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]