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.
-- 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.
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.
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
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')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With