Select if, days to birthday

user_15941046 0
Viewed 48

I recently started learning MySQL and I need to write a query using select if. The goal is to show the days to birthday for every person in the table.

So far I have the following code:

select datediff(date(concat(year(now()), '-', month(Person.Birthday), '-', day(Person.Birthday))),now()) as 'Days to Birthday'
from Person
where datediff(
date(concat(year(now()), '-',month(Person.Birthday), '-',
day(Person.Birthday))),now()) >= 0;

So far so good. This works and I also have the version where the results are < 0.

How can I integrate these lines in the select if statement? I tried for the last two days, but I wasn't successful.

Also, I tried to get a result using case and where, but I had no luck either.

I would be happy if anyone could offer a solution!

datediff if-statement case mysql
1 Answers

You have to calculate the remaining days to birthdate in 2 ways,

  1. For the current year where the birthday is approaching
  2. For the next year where the birthday has just passed and will be coming next year.

If number of days is less than zero from #1, it means the birthdate is already passed and the days needs to be calculated by considering next year. Below is the query:

SELECT z.birthday, IF(z.less_days >=0 , z.less_days, z.more_days) AS 'Days to Birthday'
FROM 
(
   SELECT birthday,
   datediff(date(concat(year(now()), '-', month(Person.Birthday), '-', day(Person.Birthday))),now()) as less_days,
   datediff(date(concat(year(now())+1, '-', month(Person.Birthday), '-', day(Person.Birthday))),now()) as more_days
   FROM Person
 ) AS z

Note: I used the outer query to eliminate the need to recalculate days in case the IF condition returns 'true'.

Considering current date is 16 May 2021, the output would be

birthday    Days to Birthday
----------------------------
2001-06-01        16
2002-07-20        65
2003-05-01        350
2004-05-16        0
2004-05-15        364

Working Fiddle

user_41399670
Related