Calculating dates MySQL

[tdsmithj]

I am doing a registry system where you have to register and pay within 2 months before action is taken. I am trying to create an SQL query to sort out registered users who have yet to pay in a specific time.

i come to a solution where you have to...

select * from members where PaidStatus = 'new' && CURDATE() > DateReg + 2 months;

but iam unsure how to calculate the 2months, i thought maybe a uni time which is around 5000000 but does not work. any suggestions.

i am using mySQL5

[chazzy]

Look up the "DATE_ADD" function. It will allow you to do something like this


select columns from members where PaidStatus='NEW' and
CURDATE() > DATE_ADD(DateReg,INTERVAL 2 MONTHS);

[scragar]

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

[tdsmithj]

i have tried that, using the date_Add but it was giving me errors saying i have an error in my mysql syntax.

select * from members where PaidStatus= 'NEW'and CURDATE() > (ADD_DATE(DateReg,INTERVAL 1 MONTH));

[chazzy]

as per their guide, it should be "MONTHS" instead of "MONTH" when used in the interval clause.

[tdsmithj]

i used "MONTH" and "MONTHS" but throws an error at MONTHS.

[scragar]

you could try it without using date_add:
CURDATE() > (DateReg + INTERVAL 1 MONTHS)

[tdsmithj]

its ok i fixed it now, i put the date_add the wrong way. simple mistake....