Page 1 of 1
Obtaining users who've had a birthday in the last 30 DAYS
Posted: Tue Sep 06, 2005 4:26 am
by Fourtet
Why have I had so much trouble with this? I have date of birth logged as a date field in the YYYY-MM-DD format. I can easily return results of users who have a birthday on the current date, but cant put a query together which will return users who've had birthdays in the last 30 days, or visa-versa (in the NEXT 30 days).
If someone could help me out here I'd be ever grateful.
Posted: Tue Sep 06, 2005 4:34 am
by feyd
the DATE_SUB function helps with this kind of operation .. alternately, you could give MySQL the timestamps to compare against using a between..
Posted: Tue Sep 06, 2005 4:41 am
by Fourtet
I've tried using DATE_SUB, I ended up just asking for people who've been born in the past 30 days though.
Code: Select all
SELECT * FROM contacts WHERE DATE_SUB(CURDATE(),INTERVAL 31 DAY) <= contactdateofbirth
I think I'm nearly there, all that is missing is distinction between the month/date rather than searching on the actual DOB. I'm not sure how to complete it though and that's pretty much where I'm stuck at the moment.
Posted: Tue Sep 06, 2005 5:03 am
by Fourtet
Hmm, still can't crack it.
Code: Select all
SELECT * FROM contacts WHERE DAY(DATE_SUB(CURDATE(),INTERVAL 31 DAY)) <= contactdateofbirth AND MONTH(DATE_SUB(CURDATE(),INTERVAL 31 DAY)) <= contactdateofbirth
Thought that might be it but no luck. Can't believe it's proving this difficult and no-one seems to know how it's done. (I've been asking around since 6am this morning)
Posted: Tue Sep 06, 2005 5:28 am
by Fourtet
Got the bastard. After 30 minutes solid hacking about in mysql console.
Code: Select all
SELECT * FROM contacts WHERE MONTH(contactdateofbirth) BETWEEN MONTH(DATE_SUB(CURDATE(),INTERVAL 31 DAY)) AND MONTH(CURDATE())
Simple in the end, i was trying to overcomplicate it by having both the day and the month in the query when only searching for birthdays in the last month. That's a relief to put it mildly!

:)
Posted: Tue Sep 06, 2005 9:28 am
by feyd
congratulations... I knew you could figure it out.
