Scott Haneda wrote:
SELECT prod_name, sale_price, sale_schedule_status
FROM products
WHERE NOW() NOT BETWEEN(sale_start AND sale_end)
AND sale_schedule_status = 'active';
I am betting this is just some parens I am missing, but the above, does find
the records where the date range in NOT BETWEEN the start and end, however,
it also finds records where the status is NOT 'active', which I don't want.
Swapping the order of the condition seems to give me an error as well, so I
am stumped.
BETWEEN min AND max. Your min is "sale_start AND sale_end" due to your use of
parentheses, so your max is "sale_schedule_status = 'active'". Assuming
sale_start and sale_end are non-zero, I expect this parses as
NOW() NOT BETWEEN 1 AND 0
for inactive rows and
NOW() NOT BETWEEN 1 AND 1
for active rows.
I think you want
SELECT prod_name, sale_price, sale_schedule_status
FROM products
WHERE (NOW() NOT BETWEEN sale_start AND sale_end)
AND sale_schedule_status = 'active';
Of course, that has no chance of using an index on sale_start or sale_end.
NOW() BETWEEN sale_start AND sale_end
is equivalent to
sale_start <= NOW() AND sale_end >= NOW()
NOT(A AND B) is equivalent to (NOT A OR NOT B), so "NOW() NOT BETWEEN ..." is
equivalent to "sale_start > NOW() OR sale_end < NOW()". Can sale_start be
greater than NOW() in your data? If not, we can simplify to
SELECT prod_name, sale_price, sale_schedule_status
FROM products
WHERE sale_end < NOW()
AND sale_schedule_status = 'active';
Can sale_end be >= NOW()?
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
