Similiar to DATE_ADD function

[b1122]

Hi all,
how can i select a record for last month or last x month only.
example :
if today is 21 sept, i only want last 2 month data,
i will get all date in aug and all date in july in my record.
I use MySQL

Thanks for any reply

[A1ien51]

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

Eric

[b1122]

I appreciate your help and time.
but it does not help me.
from the link you give to me, only can add, base on current date, not current month.(if i'm not wrong)
what i want is, add xx month base on current month.

[chazzy]

you can add months

DATE_ADD(CURDATE(),INTERVAL 3 MONTHS)

don't look at it from just the current month perspective, let mysql handle the date arithmetic for you.

[b1122]

you can add months

DATE_ADD(CURDATE(),INTERVAL 3 MONTHS)

don't look at it from just the current month perspective, let mysql handle the date arithmetic for you.

Hi Chazzy,
Have u try that script ?
say u do DATE_ADD(CURDATE(),INTERVAL 3 MONTHS) and now is 22 sept.
the mysql will add 91/92 days exactly from 22 sept.

what i want is : if today is 22 sept, interval 3 month, they give me, exactly full, days of december month.

btw, thanks for your reply and time.

[chazzy]

Have you looked at the link that eric gave you earlier? date_add and adddate are both on there. from the description, this is what they say about adding months


If you add MONTH, YEAR_MONTH, or YEAR and the resulting date has a day that is larger than the maximum day for the new month, the day is adjusted to the maximum days in the new month:

mysql> SELECT DATE_ADD('1998-01-30', INTERVAL 1 MONTH);
-> '1998-02-28'

[b1122]

Have you looked at the link that eric gave you earlier? date_add and adddate are both on there. from the description, this is what they say about adding months

Hi Chazzy,
maybe you not really get what i want.
say in my table, there is a date field, 'date of purchase'
2006-06-15
2006-06-16
2006-06-26
2006-06-28
2006-07-01
2006-07-06
2006-07-26
2006-07-28
2006-08-05
2006-08-06
2006-08-26
2006-08-28
2006-09-05
2006-09-06
2006-09-26
2006-09-28
the story is, i only know, that i want all record on jul.
but the calculation is 2 month before this month(sept).
from my query, i only this 2/ 3/ 4 var.(how many month before they want show the see want see the report)
so if i do like this :
SELECT .... where date_field >= DATE_ADD('2006-09-25', INTERVAL $xx MONTH) and date_field <= DATE_ADD('2006-09-25', INTERVAL $xx MONTH);
the record of 2006-07-01,2006-07-06 will not capture, beacause the system only get exactly minus 61 day from the current date.
Hope you know what i want.

[chazzy]

so essentially you want to have user input the month and year, and get the orders between 2 months before and that month/year, right? if so, you will probably want to use a date format function or to date function, also seen on the link we've been showing you.