Jump to content

Selecting members who's birthday is in the next month


denno020

Recommended Posts

In my database I have a field for DOB for each of the records. This field is of mysql type 'date'. I would like to have a script to select only the members who's birthday it will be within the next month.. I'm very unsure as to how to approach the comparison of the current date and the date that is stored in the database...

 

Anyone able to help me?

 

Thanks

Denno

Link to comment
Share on other sites

wow that is one confusing line; "first day of this month next month".. What does that convert into?

 

I've been Googling and I've come up with this code that will select everyone who's birthday it is in the current month, however I don't want to show people who's birthdays have already passed, and I would like the birthdays from next month, if within 30 days from the current date, to be displayed.. This overlap of months/dates is where I'm falling short..

 

However I'm unsure if your code would achieve what I'm after?

 

Here is the code I'm using at the moment..

SELECT id, name, DOB FROM pp_members WHERE monthname(DOB) = monthname(curdate()) ORDER BY DOB ASC

 

Thanks

Denno

Link to comment
Share on other sites

I have managed to solve the problem myself :D.

 

What I did was convert the date from the database into it's day-of-the-year equivalent, and then compared it to the day-of-the-year of the current date. This worked perfectly :).

 

Here it is:

SELECT id, name, DOB FROM pp_members WHERE dayofyear(DOB) < dayofyear(curdate())+30 && dayofyear(DOB) > dayofyear(curdate()) ORDER BY dayofyear(DOB) ASC

 

I'm quite happy with this result :D.

 

Thanks for the help though.

Denno

Link to comment
Share on other sites

denno, strtotime converts an English date sentence into a date. You need to have 'first day of this month next month' instead of just 'next month' or '+1 month' because if you're on 31 January then +30 days will become March and you'll skip February completely. So what that does is first gives you 1st day of the month and then goes +30 days which guarantees not to skip month. The 'n' is a format that results in a month number 1-12 without a leading zero, a value that the MySQL MONTH() function understands.

Link to comment
Share on other sites

Get the NEXT MONTH from the current date, make sure that if you are on Jan, the next month will be 01, and not 13, make sure you change the year also...

 

Get the MONTH from the DOB, check with your date var.. 

 

 

TADAHHHHHH you have a code running.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.