mySQL SELECT upcoming birthdays

by

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

More Related Contents:

  1. Which DB technology is sued to solve the following search issue? [closed]
  2. How to select the nth row in a SQL database table?
  3. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  4. Error Code: 2013. Lost connection to MySQL server during query
  5. What’s faster, SELECT DISTINCT or GROUP BY in MySQL?
  6. Storing JSON in database vs. having a new column for each key
  7. How do I restore a dump file from mysqldump?
  8. Using an Alias in SQL Calculations
  9. Can a table field contain a hyphen?
  10. Adding a leading zero to some values in column in MySQL
  11. View’s SELECT contains a subquery in the FROM clause
  12. Check for x consecutive days – given timestamps in database
  13. MySQL Insert Into from one Database in another
  14. How do I get the ID of multiple inserted rows in MySQL?
  15. Does mysql have the equivalent of Oracle’s “analytic functions”?
  16. Case statement in MySQL
  17. Replace Into Query Syntax
  18. MySQL: LAST_INSERT_ID() returns 0
  19. Remove special characters from a database field
  20. Using reserved words in column names
  21. MySQL query, MAX() + GROUP BY
  22. Calculating a Moving Average MySQL?
  23. MySQL – Replace Character in Columns
  24. Strings as Primary Keys in MYSQL Database [closed]
  25. MySQL : ERROR 1215 (HY000): Cannot add foreign key constraint
  26. FIND_IN_SET with multiple value [duplicate]
  27. Foreign key constraints: When to use ON UPDATE and ON DELETE
  28. What is the difference between SQL and MySQL? [closed]
  29. How to store a one to many relation in MySQL database?
  30. MySQL – Complexity of: SELECT COUNT(*) FROM MyTable;

Leave a Comment