Finding date a year later

[mattyvx]

Hi!

 

Im creating a CronJob and need a query that will return any members who fall within one week of their 12 month membership.

 

The date they join is recorded as Joined.

 

Something like:

 

"Select Name,Email,Joined From Users WHERE Joined = Joined + 358 DAYS";

 

Thanks in advance

[delayedinsanity]

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-add

 

SELECT name, email, joined FROM `users` WHERE joined = DATE_SUB( NOW(), INTERVAL -51 week )

 

For an example. Of course you'll have to figure out the formatting, and you'll probably want to SUBSTR your date field, because this by itself will look for an entry with the exact same time of day. You'll want to search by just the day, I'm sure.

 

It might be easier in the future if when the member is added to the database you included a column with the expiration date. Then you could avoid the SQL-FU.

 

[abazoskib]

SELECT name, email, joined FROM `users` WHERE joined BETWEEN CURDATE()- INTERVAL 51 week AND CURDATE()- INTERVAL 52 week

 

The query delayedinsanity posted wont work. Try the above query out.

[mattyvx]

thanks for your help. I've tried them both and with a bit of changing round ;

 

SELECT name, email, Joined FROM `Users` WHERE Joined = CURDATE()- INTERVAL 358 day

 

That works.

 

abazoskib, your method worked (if i swapped the dates round) but it would return the record every day between that region. I want this as a one off so I can send a reminder a week before subscription ends.

 

Thanks again!