Upcoming birthday of users between today and x days

Question

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

kiks73 · Answer 1 · 2017-10-13T10:44:16.657

2

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)

edited Oct 13 '17 at 10:44

answered Oct 13 '17 at 10:00

kiks73

3,718
3
25
52

But this would also return people who's birthday is `1980-09-13` and `1980-08-13` and so on.. you also have to consider the month and if you're at the and of the month also the next month etc... – Jan Zeiseweis Oct 13 '17 at 10:14
It showing next month birthday too not next 2 days: please check, http://sqlfiddle.com/#!9/11696/1 – Oct 13 '17 at 10:18
@JanZeiseweis I've edited my answer. I forgot to replace DAY with DAYOFYEAR – kiks73 Oct 13 '17 at 10:18
@JanZeiseweis Yes the same problem – Oct 13 '17 at 10:19
@mkrahamath it is showing birthdays from today to +2 days, if want to start from tomorrow replace 0 with 1 – kiks73 Oct 13 '17 at 10:26
Please check this: http://sqlfiddle.com/#!9/56dc1a/1 you will see Yesterday result also – Oct 13 '17 at 10:28
`dayofyear` should work better, not sure about '2016-02-29' ;). But this has been asked before actually: https://stackoverflow.com/questions/18747853/mysql-select-upcoming-birthdays – Jan Zeiseweis Oct 13 '17 at 10:28
it works if i set between 1 and 3, is this proper way? – Oct 13 '17 at 10:30
It shows the `1996-10-12` because 1996 was a leap year. So in terms of day of year they are equal (`SELECT dayofyear('1996-10-12'), dayofyear('2017-10-13');` returns 286 for both). – Jan Zeiseweis Oct 13 '17 at 10:31
@JanZeiseweis Your are right. I've edited my answer as suggested in the SO question mentioned. – kiks73 Oct 13 '17 at 10:47

Jan Zeiseweis · Answer 2 · 2017-10-13T10:11:32.037

0

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 ;

edited Oct 13 '17 at 10:11

answered Oct 13 '17 at 09:55

Jan Zeiseweis

3,718
2
17
24

Thank you for answer but i want to more simplified query. – Oct 13 '17 at 10:31

score 0 · Accepted Answer · answered Oct 13 '17 at 10:33

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

Upcoming birthday of users between today and x days

3 Answers3