First off, I don't think you should use 'date' as a column name; isn't it
a reserved word?
Then, hmm.. is that column a DATE column, or DATETIME, or TIMESTAMP?
If it is DATETIME or TIMESTAMP, then you don't really need the DATE_FORMAT()
conversion; compaing a DATETIME or TIMESTAMP value directly to a DATE_SUB()
return value works perfectly well.
Actually, I just checked it, and comparing a DATE value against a DATE_SUB()
return value works just as well; so my advice is, lose the DATE_FORMAT(),
try field >= DATE_SUB(NOW(), INTERVAL 30 DAY) directly.
G'luck,
Peter
--
If I were you, who would be reading this sentence?
On Tue, Apr 10, 2001 at 09:47:40AM +0200, David Bouw wrote:
> 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..
---------------------------------------------------------------------
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
