Calculate how many months in MySQL

Question

I am creating a child registration system and I need to find out how many months the children are. This is pretty easy, but I only want to extract children who are "month of birth".

What I mean by "month of birth" is if they were born on November 23, and December 23 will be their 1 month of birth. January 23rd will be their 2-month birthday. And so on...

Now my code below tells me how many months they are, but whether this is their "month of birth."

Any ideas how I can make this work?

SELECT PERIOD_DIFF( DATE_FORMAT('2011-11-23', '%Y%m'), DATE_FORMAT(birthday.meta_value, '%Y%m') ) AS months FROM $db->members AS m JOIN $db->members_meta AS birthday ON birthday.member_id = m.id AND birthday.meta_key = 'birthday' AND birthday.meta_value != '' WHERE AND m.deleted = 0 GROUP BY m.id 

I think we will need to consider whether they are born in leap years (February 29), 30 and 31 months. I found this code that works for thei search

 ( 2012 % 4 <> 0 OR ( 2012 % 100 = 0 AND 2012 % 400 <> 0 ) ) AND '11-23' = '03-01' // 11-23 is the current month and day AND DATE_FORMAT(birthday.meta_value, '%m-%d') = '02-29' 

Answer 1

The request below is completed and tested. It should work for you if you want to see all those who have the bday month today.

Note that you must know if today is the last day of the month, and you must know if they were born on the last day of the month.

Assuming you have an id column, this should do it for you. I checked this query and I believe that it does what you want. If not, leave a comment so I can better understand your requirements.

 SELECT `id` FROM $db->members AS m JOIN $db->members_meta AS birthday ON birthday.member_id = m.id AND birthday.meta_key = 'birthday' AND birthday.meta_value != '' WHERE ( -- If it a match, it a match DATE_FORMAT(birthday.meta_value, '%d') = DATE_FORMAT('2011-11-23', '%d') OR IF ( -- If today is the last day of this month, we want to close out the month. DATEDIFF(LAST_DAY('2011-11-23'), '2011-11-23') = 0, -- Match all birthdays that have a day greater than today. DATE_FORMAT(birthday.meta_value, '%d') > DATE_FORMAT('2011-11-23', '%d'), 0 ) OR ) AND m.deleted = 0 GROUP BY m.id ; 

Answer 2

So, in order to determine whether a given date is a child of the “month of birth”, we need to check whether the day of the month matches exactly or what, if the birthday of the month is more than the last day of the month we are addressing, we check whether our date is the last day of the month in which we check, MINUS is the number of days between the date of birth and the last day of the month of birth!

This last bit may seem crazy, but I think it gives you the opportunity to have more than one “month of birth” per month that has fewer days than your month of birth (see my second comment on the question above).

I will look for something in these lines, sorry syntax, I don't have a MySQL database:

 SELECT m.id FROM $db->members AS m JOIN $db->members_meta AS birthday ON ( birthday.member_id = m.id AND birthday.meta_key = 'birthday' AND birthday.meta_value != '' ) WHERE DAY(birthday.meta_value) = CASE WHEN (DAY(birthday.meta_value) > DAY(LAST_DAY(CURDATE()))) THEN LAST_DAY(CURDATE()) - DATEDIFF(birthday.meta_value, LAST_DAY(birthday.meta_value)) ELSE DAY(CURDATE()) END CASE 

Please note that it really doesn't matter to me whether this is a leap year or not.