MySQL get next instance of a date of birth / birthday

Question

I am trying to find the next instance of a date in MySQL based on a DOB - for example, if the DOB is 1975-12-20 and we are running it today then the next birthday is 2020-12-20.

Likewise if the DOB is 1975-12-19 then the next birthday is 2020-12-19 but if the DOB is 1975-12-21 then the next birthday is 2019-12-21.

I can do this in PHP by exploding the date and replacing the year but I want to try and do it in MySQL as it will be quicker to run etc

DOB is a date field 0000-00-00

I found How to calculate next birthday given a date of birth? for postgressql which I thought would work but it is using a function called age which doesnt seem to work in MySQL

This could help you replace the age function: https://stackoverflow.com/a/19522129/4461980 and then it's basically the same query as in the question you linked to — Florian Humblot, Dec 20 '19 at 10:40
Also note that doing this in mysql isn't necessarily faster, have you benchmarked it? Don't forget that a few microseconds in enhanced speed might not outweigh the benefits of easy maintainability you'd get from doing it in the backend — Florian Humblot, Dec 20 '19 at 10:42
I cannot benchmark yet as I do not have the MySQL version working yet but I will — bhttoan, Dec 20 '19 at 11:00
29th Feb is an interesting one - some I assume will treat 28 Feb as their birthday and some 1st March in non leap years so I dont' think there is a perfect answer? — bhttoan, Dec 20 '19 at 11:01

score 0 · Answer 1 · answered Dec 20 '19 at 10:47

You can do date arithmetic as follows:

dob
+ interval (
    year(curdate()) 
    - year(dob) 
    + (dayofyear(curdate()) > dayofyear(dob))
) year

Basically, this takes the differences between the current date and the year of birth, and adds 1 if the birthday of the current year is in the past. This gives you the number of years to add to the dob to get the next birthday.

When it comes to February 29th, the above expression would return February 28th on non-leap years.

score 0 · Answer 2 · answered Dec 20 '19 at 10:52

0

Created a fiddle with sample data and query to get the next birthday date. Sharing below is query and sql fiddle link:

select *, 
IF(month(dob) = month(now()) AND day(dob) > day(now()), concat(Year(now()),'-',month(dob),'-',day(dob)) ,concat(Year(now())+1,'-',month(dob),'-',day(dob))) as nextBirthDay
from TableName

Sql fiddle: http://sqlfiddle.com/#!9/033ad4/18

answered Dec 20 '19 at 10:52

Suresh Kamrushi

15,627
13
75
90

This does work for me - one thing is that the dates come back as 2020-1-1 for example. Can they come back as 2020-01-01? – bhttoan Dec 20 '19 at 11:00
you can do that, but again it will be very difficult to manage in future. I will suggest do it in php, it will be single liner. – Suresh Kamrushi Dec 20 '19 at 11:02

score 0 · Answer 3 · answered Dec 20 '19 at 11:09

0

Maybe this way. For the current year.

select DATE_ADD(dob, INTERVAL TIMESTAMPDIFF(year, dob, now()) + (dayofyear(dob) > dayofyear(now())) YEAR) as result

In sandbox https://paiza.io/projects/iCDmx0RP1jck56xXzGu2Jw?language=mysql

answered Dec 20 '19 at 11:09

Alexey Usharovski

1,404
13
31

MySQL get next instance of a date of birth / birthday

3 Answers3