Hi,

I have to select elements that are not older than 2 months (=60 days).
The table has a standard DATE column with format (YYYY-MM-DD), called
'date'.

I tried a lot of queries, one of them is following:

SELECT * FROM mytable WHERE date > (SYSDATE()-60);

So basically i'm telling mysql to select elements where the date is
bigger (= older) than the actual system date diminished with 60 days.
But it does't return anything (and yes I have elements newer than 2
months in the table :-)

I suppose something is wrong with the use of SYSDATE. Anyone any
experience with this ?

b.coolsaet (AT) gmail (DOT) com wrote:

The user manual has a lot of experience of this and explains how to do
it.

Think about it. SYSDATE() "Returns the current date and time as a value
in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether
the function is used in a string or numeric context." (quoted from
manual)

If you simply substract 60 from this, how is MySQL to know 60 what?
60 years?
60 seconds?
60 bars of chocolate?

Try:
SELECT * FROM mytable WHERE date > (SYSDATE()-INTERVAL 60 DAY);

as the manual suggests
http://dev.mysql.com/doc/refman/5.0/...functions.html

I think that it would be better with

SELECT * FROM mytable WHERE date > adddate(sysdate(), -60)

Which is a spin off from "date_add(date, -60, DAY)"

You can swap DAY for anything like:

MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR


Hope that helps.


"Captain Paralytic" <paul_lautman (AT) yahoo (DOT) com> wrote

Sean wrote:

And why wouldn't you use subdate rather than adddate with a negative
argument?

"Captain Paralytic" <paul_lautman (AT) yahoo (DOT) com> wrote

Sorry, you're right.

I was implying that this would be a better option than SYSDATE()-INTERVAL 60
DAY and overlooked "sub_date", which of course makes even more sense.

SA

Sean wrote:

of
SYSDATE()-INTERVAL 60 ?