Your opinion please : next birthdays within 7 days

suntra

Hi.

I just had some fun trying to build a MySQL query that will return birthdays for the 7 coming days including today... I cared about New Year... I'd just like to know what you think of it...

SELECT
	p84ue.user_id, 
	p84ue.Anniversaire,
	IF(month(anniversaire) = 1 AND month(NOW()) = 12, INSERT(SUBSTRING(Anniversaire, 6, 5), 1, 2, 13), SUBSTRING(Anniversaire, 6, 5)) as Jour_Mois
FROM
	p84_users_extras p84ue
WHERE
	IF(month(anniversaire) = 1 AND month(NOW()) = 12, INSERT(SUBSTRING(Anniversaire, 6, 5), 1, 2, 13), SUBSTRING(Anniversaire, 6, 5)) >= SUBSTRING(NOW(), 6, 5)
    	AND
	IF(month(anniversaire) = 1 AND month(NOW()) = 12, INSERT(SUBSTRING(Anniversaire, 6, 5), 1, 2, 13), SUBSTRING(Anniversaire, 6, 5)) < INSERT(SUBSTRING(DATE_ADD(NOW(),INTERVAL 7 DAY), 6, 5), 1, 2, IF(month(DATE_ADD(NOW(),INTERVAL 7 DAY)) < month(NOW()), '13', '12'))
ORDER BY
	Jour_Mois ASC

To make it short, it looks at the month and day (mm-dd). That has to be higher or equal to the current month and day, and must be inferior to today + seven days. But if the current month is 12 and the month of a row in the table is 01, it is changed to 13, that's why the query is so long...

Installer

If, as you say, you just want the records with birthdays in the next 7 days, wouldn't something like this work:

SELECT
  p84ue.user_id, 
  p84ue.Anniversaire
WHERE
  DATEDIFF(Anniversaire, NOW()) >= 0 AND DATEDIFF(Anniversaire, NOW()) <= 7
ORDER BY
  Anniversaire ASC

suntra

I didn't know about the datediff function. Thanks 😃

But, then I have to modify the year in "Anniversaire", and what about december vs january ?!

suntra

Now it looks like this :

SELECT
   p84ue.Anniversaire,
   DATEDIFF(INSERT(Anniversaire, 1, 4, IF(month(Anniversaire) = 1 AND month(NOW()) = 12, YEAR(NOW()) + 1, YEAR(NOW()))), NOW()) AS Delta_Jours
FROM
   p84_users_extras p84ue
WHERE
   DATEDIFF(INSERT(Anniversaire, 1, 4, IF(month(Anniversaire) = 1 AND month(NOW()) = 12, YEAR(NOW()) + 1, YEAR(NOW()))), NOW()) >= 0
AND
    DATEDIFF(INSERT(Anniversaire, 1, 4, IF(month(Anniversaire) = 1 AND month(NOW()) = 12, YEAR(NOW()) + 1, YEAR(NOW()))), NOW()) < 7
ORDER BY
    Delta_Jours ASC

Installer

I don't know why you have to modify anything. DATEDIFF() returns the difference in days between two dates, period. (As long as "Anniversaire" is a date, datetime, or timestamp type, which it must be since you use the MONTH() function on it.)

suntra

Simply because this...

SELECT DATEDIFF('1984-06-21', NOW())

returns this...

-8228 !

By the way, Anniversaire is a date column (YYYY-mm-dd)

Installer

OK 🙂, now I see.