Obtaining users who've had a birthday in the last 30 DAYS

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Fourtet
Forum Commoner
Posts: 29
Joined: Fri Sep 02, 2005 5:55 pm

Obtaining users who've had a birthday in the last 30 DAYS

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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..
Fourtet
Forum Commoner
Posts: 29
Joined: Fri Sep 02, 2005 5:55 pm

Post 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.

:lol: :lol:

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.
Fourtet
Forum Commoner
Posts: 29
Joined: Fri Sep 02, 2005 5:55 pm

Post 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)
Fourtet
Forum Commoner
Posts: 29
Joined: Fri Sep 02, 2005 5:55 pm

Post 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! :D:)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

congratulations... I knew you could figure it out. :)
Post Reply