CODEWITHSUNDEEP
mysql
user4198332
user4198332

Reputation:

Upcoming birthday of users between today and x days

I am trying to make simple MySQL query to display upcoming birthdays using below query. How to exclude/remove previous(yesterday) day from showing.

CREATE TABLE users (
  name VARCHAR(100),
  birthday DATE
);
INSERT INTO users (name, birthday) VALUES 
  ('kostas',  '1983-10-08'),
  ('kostas',  '1983-10-11'),
  ('yannis',  '1979-10-13'),
  ('natalia', '1980-10-15'),
  ('kostas',  '1983-10-12'),
  ('Moskas',  '1978-10-14'),
  ('Rasman',  '1978-10-13'),
  ('natalia', '1980-10-18'),
  ('natalia', '1980-10-16');

Query:

SELECT *
FROM 
  users 
WHERE 
  birthday != '' AND ABS(DAY(CURDATE()) - DAY(birthday)) < 2
ORDER BY 
  DAY(birthday)

Demo: sqlfiddle

Upvotes: 0

Views: 144

Answers (3)

kiks73
kiks73

Reputation: 3758

You have to use BETWEEN instead of ABS. The absolute value do not return what you want, the between 0 and "days before the birthday" (2) is the right way to get days until birthday.

You also have to use DAYOFYEAR instead of DAY and you have to reverse the order of the subtraction terms DAYOFYEAR(birthday) - DAYOFYEAR(CURDATE())

To workaround leap years birthdays, as suggested here, birthday year should be converted to current year with:

DAYOFYEAR(DATE_ADD(e.birthdate, INTERVAL (YEAR(NOW()) - YEAR(birthday)) YEAR))

The final SQL is:

SELECT *
FROM 
  users 
WHERE 
  birthday != '' AND (DAYOFYEAR(DATE_ADD(birthday, INTERVAL (YEAR(NOW()) - YEAR(birthday)) YEAR))-DAYOFYEAR(CURDATE())) between 0 and 2
ORDER BY 
  DAY(birthday)

Upvotes: 2

user4198332
user4198332

Reputation:

I have used this previously, hope it will help others.

SELECT name, birthday, DATE_ADD(birthday, INTERVAL IF(DAYOFYEAR(birthday) >= DAYOFYEAR(CURDATE()), YEAR(CURDATE())-YEAR(birthday), YEAR(CURDATE())-YEAR(birthday)+1) YEAR ) AS next_birthday 
FROM users
WHERE birthday!= '' AND disabled = '0'
HAVING next_birthday BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 3 DAY)
ORDER BY next_birthday

Upvotes: 0

Jan Zeiseweis
Jan Zeiseweis

Reputation: 3738

I'd do it this way: Transfer the birthdays to the current year and then define the datediff you want:

SELECT *,
       DATEDIFF(str_to_date(CONCAT(YEAR(curdate()), '-', MONTH(birthday), '-', DAY(birthday)), '%Y-%m-%d'), curdate()) AS `days until birthday`
FROM users
WHERE DATEDIFF(str_to_date(CONCAT(YEAR(curdate()), '-', MONTH(birthday), '-', DAY(birthday)), '%Y-%m-%d'), curdate()) BETWEEN 1 AND 5 ;

Upvotes: 0

Related Questions