Trouble using SYSDATE() - MySQL

[b.coolsaet@gmail.com]

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 ?

[Captain]

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/en/date-and-time-functions.html

[Sean]

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" <com> wrote in message
news:googlegroups.com... 
>
> 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/en/date-and-time-functions.html
>[/ref]

[Captain]

Sean wrote:
 
Why would it be better?
And why wouldn't you use subdate rather than adddate with a negative
argument?

[Sean]

"Captain Paralytic" <com> wrote in message
news:googlegroups.com... 
> Why would it be better?
> And why wouldn't you use subdate rather than adddate with a negative
> argument?
>[/ref]

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

[Captain]

Sean wrote:
 
> > Why would it be better?
> > And why wouldn't you use subdate rather than adddate with a negative
> > argument?
> >[/ref]
>
> 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[/ref]
But why is subdate (or date_sub) better than the opure maths approach
of
SYSDATE()-INTERVAL 60 ?