List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:July 28 2002 3:48am
Subject:Re: Date Comparison Problem...
View as plain text  
>Just when I thought I was getting the hang of this mysql stuff, the simplest
>of tasks
>is causing me to think about pulling my hair out.
>
>My table:
>
>    NEWS {
>      newsID (INT(3), AUTO-INCREMENT),
>      newsTitle (VARCHAR (50)),
>      newsText (BLOB),
>      newsDate (DATE)
>    }

It's better to use copy and paste when reporting code - what you
show there is illegal syntax.

>
>I just want to filter OUT any rows that have a 'future' date value in
>'newsDate' column.
>(ie. Don't select any news that hasn't happened yet!)
>
>Query, I gather the WHERE clause deals with YYYY-MM-DD formatted dates:
>
>    SELECT * FROM news
>      WHERE newsDate < DATE_ADD(CUR_DATE(), INTERVAL 1 DAY)
>      ORDER BY newsDate DESC

That's illegal, too.  There is no CUR_DATE() function, it's CURDATE().
Other than that, it looks okay.  If it produces no rows, I would assume
that means that you have no news that is not in the future, but I guess
that's probably not true.  You can see what dates are being compared
like this, which may help:

SELECT newsDate, DATE_ADD(CURDATE(),INTERVAL 1 DAY) FROM news;

Also, I expect that your query would be simpler like this:

SELECT * FROM news WHERE newsDate <= CURDATE() ORDER BY newsDate DESC;

(Note the <= rather than <...)

>
>If I take out the WHERE clause the whole table gets SELECT-ed, it works
>fine:
>
>    SELECT * FROM news
>      ORDER BY newsDate DESC
>
>Any assistance gratefully Rx'd.  I have DuBois's MySQL, it's obviously too
>good for me.
>Also tried various searches on MySQL.com, with little joy, aaaaaahh!
>
>Tom.

Thread
Date Comparison Problem...Tom Norwood28 Jul
  • Re: Date Comparison Problem...Paul DuBois28 Jul