Hi Peter,
The collumn is a datatime... You are tight that date probably should be is a
reserved word..
I must say that I never had any troubles with it, I started using it when I
was a newbie with PHP about a year ago...
On almost all my tables I always have an auto-increment column with the name
ID and a datetime column named DATE..
But you are right that it is probably reserved for some stuff..
The DATE_FORMAT can indeed be left out, but I once understood that this was
more save to do..???
I never asked why, but also never had any problems..
I found the source of my problem.. The table in which I didn't get the right
results was quite large..
About 3000 records.. The reason I knew that there was a mistake, was that
there was a (new) product which we bought about 50 of.. When I looked the
number sold + still in stock I couldn't get add up to 50..
It seemed that we added some code in which the current-time-stamp wasn't
stored with some orders with this product.... This way some of these
products weren't counted because they had the timestampt 0000-00-00
00:00:00..
Founded this out when I was looking throught the table..
Thanks for the help..
Bye Bye
David
> 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
