RE: WHERE clause problem
Try using IN(Month(Now()), Month(Now()) -1) -Original Message- From: mayuran To: [EMAIL PROTECTED] Sent: 5/3/04 10:15 AM Subject: WHERE clause problem 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: WHERE clause problem
Hmm. Bit Odd. However, I suspect the problem is that your 'where' isn't explicit enough: Where ( month(date) = month(now()) ) or ( month(date) = month(now())-1 ) Bear in mind that if month(now()) = 1 you will be looking for records in month 0! A better way to do this might be: WHERE month(date) BETWEEN month(now() - interval 1 month) AND month(now) Cheers, Matt -Original Message- From: mayuran [mailto:[EMAIL PROTECTED] Sent: 03 May 2004 16:15 To: [EMAIL PROTECTED] Subject: WHERE clause problem 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: WHERE clause problem
From: mayuran [mailto:[EMAIL PROTECTED] 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 :) The first one, while it may look valid, isn't. `MONTH(NOW())' currently evaluates to 5. `MONTH(NOW())-1' currently evaluates to 4. As such, your first query is essentially the following: SELECT * FROM testing WHERE MONTH(date) = (5 OR 4); Which becomes: SELECT * FROM testing WHERE MONTH(date) = 1; `(MONTH(NOW()) OR MONTH(NOW())-1)' aka `(5 OR 4)' evaluates to 1, because it's just ORing two integers. Note that it's returning exactly what it's supposed to be returning, the date in January. So while it may look like it makes sense in pseudo-code, on paper it's not how MySQL evaluates things. It builds the right side of the equality and then compares. Stick with your second query and you'll be fine, even if it is more typing. ;) HTH! -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: WHERE clause problem
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]
Re: WHERE clause problem
At 11:15 -0400 5/3/04, 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. 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]
