Hi there,
I don't seem to understand the DATE_SUB / INTERVAL function..
I have a query which basically retrieves data from a table which consists of
ordernumers and quantities..
In the following example everything of ordernr 'te-004' should be retrieved,
quantities counted and this should be done only for the last 30 days..
When I remove the pieces which tries to get the last 30 days everything
works fine (except that everything in the database is counted..) When I add
the date 'piece' I can't seem to figure out what goes wrong..
In any case I don't get the result I expect. (Not everything is counted..)
SELECT perfect_customer_productorders.ordernr,
Sum(perfect_customer_productorders.quantity) AS quantity FROM
perfect_customer_productorders left join perfect_customer_orders on
perfect_customer_productorders.orderid = perfect_customer_orders.id where
((perfect_customer_productorders.date) >= DATE_FORMAT( DATE_SUB( NOW() ,
INTERVAL 30 DAY ) , '%Y-%m-%d' ) and perfect_customer_productorders.ordernr
= 'te-004' and perfect_customer_orders.status != 2) GROUP BY
perfect_customer_productorders.ordernr ORDER BY quantity desc
-> This is the trouble maker: (perfect_customer_productorders.date) >=
DATE_FORMAT( DATE_SUB( NOW() , INTERVAL 30 DAY ) , '%Y-%m-%d' )
My question: How do I get an date which is 30 days back from now..?
I have PHP, so I could basically also use mktime to generate a date 30 days
back.. But I think it's nicer to do this in MySQL
Thanks in advance for all the help..
Bye Bye
David
Bye Bye
David
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
