Is it possible to create a single sql statement to fetch a list of people where their birthday celebration will fall in next 60 days

Question

I have a table where it has some name and dateofbirth.

Refence data:

ABC, 1990-11-23
BCD, 1998-10-21
CDE, 1997-05-02
DEF, 2000-10-15
EFG, 1999-01-10
FGH, 1987-01-15
GHI, 1989-12-19
HIJ, 1986-12-09

I need a SQL query where I need to get the birthday celebration dates that is going to happen during the next 60 days ordered by celebration dates.

This is the query that I used till now.

SELECT * 
FROM `friends` 
WHERE ( DATE_FORMAT(`dob`, '%m%d') >= DATE_FORMAT(CURDATE(), '%m%d') 
AND DATE_FORMAT(`dob`, '%m%d') <= DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 60 DAY), '%m%d') 
ORDER BY DATE_FORMAT(`dob`, '%m%d');

It works ok if it runs during Jan to Oct. During November and December, the condition DATE_FORMAT(dob, '%m%d') <= DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 60 DAY), '%m%d') cannot apply. For example, the resulting comparison will be like 1209 < 0131 and fails.

The result that I expect to get when executed on Dec 2, 2022 is

HIJ, 1986-12-09
GHI, 1989-12-19
EFG, 1999-01-10
FGH, 1987-01-15

How do I do this in one single query?

See this thread https://stackoverflow.com/questions/83531/sql-select-upcoming-birthdays — PeterClemmensen, Dec 02 '22 at 15:07
select 'X' where datediff('2022-12-01',curdate()) between 0 and 60; — Simon Goater, Dec 02 '22 at 15:37
Simon Goater's comment should be the answer. In general if you want to compare dates, use date functions. Converting to strings and doing string comparison will only cause problems. — Robert Hamilton, Dec 02 '22 at 22:44

VvdL · Answer 1 · 2022-12-02T16:53:07.617

The thread mentioned in the comment to your question uses things like adding 365.25 days to get this to work. I think this solution might be more reliable.

You can construct this years' birthday by extracting the month and day from the date of birth, and concatenating the current year to it using STR_TO_DATE.

Then you can check using a CASE statement if this years' birthday has already passed, in which case you add a year to that birthday, because that will be the next birthday for name. Then you can check if the result of that CASE statement is BETWEEN today and 60 days from now.

I used a CTE to make it clearer to read. DBfiddle here.

WITH cte as (
  SELECT
    -- First determine this years (year of current date) birthday
    -- by constructing it from the current year, month of birth and day of birth
    STR_TO_DATE(
      CONCAT(YEAR(CURDATE()),'-', MONTH(dob), '-', DAY(dob)), 
      '%Y-%m-%d') AS this_years_birthday, 
    name, 
    dob
  FROM friends
)
SELECT cte.name, cte.dob
FROM cte
WHERE 
    -- If the birthday is still in this year
    -- Use this years' birthday
    -- else add a year to this years' birthday
    -- Then filter it to be between today and 60 days from now
    CASE WHEN this_years_birthday >= CURDATE() 
    THEN this_years_birthday 
    ELSE DATE_ADD(this_years_birthday, INTERVAL 1 YEAR) END 
    BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 60 DAY)
ORDER BY MONTH(cte.dob) DESC

name	dob
GHI	1989-12-19
HIJ	1986-12-09
EFG	1999-01-10
FGH	1987-01-15

Is it possible to create a single sql statement to fetch a list of people where their birthday celebration will fall in next 60 days

1 Answers1