Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mySQL SELECT upcoming birthdays

I'm trying to write a query to select users of a database whose birthdays are in the next 7 days.

I've done a lot of research but I can't come up with a working solution.

The birthday field is stored as a varchar eg '04/16/93' is there any way to work with this?

This is what I have so far:

SELECT * 
FROM   `PERSONS` 
WHERE  `BIRTHDAY` > DATEADD(DAY, -7, GETDATE()) 

I should have made it more clear, I'm trying to find birthdays not dates of birth. So i'm just looking for days and months not years.

like image 476
Danny Avatar asked Sep 11 '13 17:09

Danny


People also ask

How do I select a birthday in SQL?

-- Query to find workers, whose birthday is in current week SELECT * FROM @Workers WHERE DATEPART( Week, DATEADD( Year, DATEPART( Year, GETDATE()) - DATEPART( Year, DOB), DOB)) = DATEPART( Week, GETDATE()); The basic idea is the same as in the first query I explained.

What is Getdate () in MySQL?

This function in SQL Server is used to return the present date and time of the database system in a 'YYYY-MM-DD hh:mm:ss.


2 Answers

To get all birthdays in next 7 days, add the year difference between the date of birth and today to the date of birth and then find if it falls within next seven days.

SELECT * 
FROM  persons 
WHERE  DATE_ADD(birthday, 
                INTERVAL YEAR(CURDATE())-YEAR(birthday)
                         + IF(DAYOFYEAR(CURDATE()) > DAYOFYEAR(birthday),1,0)
                YEAR)  
            BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY);

If you want to exclude today's birthdays just change > to >=

SELECT * 
FROM  persons 
WHERE  DATE_ADD(birthday, 
                INTERVAL YEAR(CURDATE())-YEAR(birthday)
                         + IF(DAYOFYEAR(CURDATE()) >= DAYOFYEAR(birthday),1,0)
                YEAR)  
            BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY);

-- Same as above query with another way to exclude today's birthdays 
SELECT * 
FROM  persons 
WHERE  DATE_ADD(birthday, 
                INTERVAL YEAR(CURDATE())-YEAR(birthday)
                         + IF(DAYOFYEAR(CURDATE()) > DAYOFYEAR(birthday),1,0)
                YEAR) 
            BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)
     AND DATE_ADD(birthday, INTERVAL YEAR(CURDATE())-YEAR(birthday) YEAR) <> CURDATE();


-- Same as above query with another way to exclude today's birthdays 
SELECT * 
FROM  persons 
WHERE  DATE_ADD(birthday, 
                INTERVAL YEAR(CURDATE())-YEAR(birthday)
                         + IF(DAYOFYEAR(CURDATE()) > DAYOFYEAR(birthday),1,0)
                YEAR) 
            BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)
     AND (MONTH(birthday) <> MONTH(CURDATE()) OR DAY(birthday) <> DAY(CURDATE()));

Here is a DEMO of all queries

like image 125
Praveen Lobo Avatar answered Sep 20 '22 11:09

Praveen Lobo


Its very easy and simple. No need to use any if conditions or anything else you just need to use DATE_FORMAT() function of mysql.

Here is my sql query that is

SELECT id,email ,dob FROM `users` where DATE_FORMAT(dob, '%m-%d') >= DATE_FORMAT(NOW(), '%m-%d') and DATE_FORMAT(dob, '%m-%d') <= DATE_FORMAT((NOW() + INTERVAL +7 DAY), '%m-%d')

like image 26
Rakesh Avatar answered Sep 17 '22 11:09

Rakesh