How to search birthdays dates using only day and month

Question

I've been looking for a solution here in the community, but I have not found anything specific. I need to do searches for birthdays among a period using only the month and month day (excluding year). I have users with their birthdays registered in date fields in my MySQL.

Name | Dt. Birth
Julian | 1990/01/18
Luiz | 2000/02/15
Morgana | 1973/02/01

I need to realize two different tasks.

1st - Let's suppose today date is February 01. How could I take today's date and decrease 14 days, searching for users whose birthday was on previous 2 weeks? (only Julian would be listed).

Another query will list users whose birthday is today (only Morgana would be listed).

And a third one should list the birthdays within next two weeks (only Luiz would be listed) I was using these code, but they don't seem to work at all times.

select * from user where DAYOFYEAR(dateBirth) between DAYOFYEAR(CURDATE())-14 and DAYOFYEAR(CURDATE())-1;
select * from user where DAYOFYEAR(dateBirth) = DAYOFYEAR(CURDATE());
select * from user where DAYOFYEAR(dateBirth) between DAYOFYEAR(CURDATE())+1 and DAYOFYEAR(CURDATE())+14;

2nd - In a form, I will select two days and months
|01||January |
|15||February|
and the users whose birthday is within this range will be listed. (All users listed)

Maybe this fiddle help you visualize the question. http://www.sqlfiddle.com/#!9/1dec7a/5
I reproduce the above queries that show some inaccuracy. If you change values (-14/+14 and (-1/+1), you shall be able to list the users.

store dates as [dates](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-types.html), in mysql, this will make your life infinitely easier — , Jun 17 '18 at 20:58
this is what i think you are going for: https://stackoverflow.com/questions/18747853/mysql-select-upcoming-birthdays — , Jun 17 '18 at 21:05
These queries take the day of year to make the comparison, at some times, It list users out of right range (1 day more, or 1 day less). Some relation with leap years maybe? — Guilhermy, Jun 17 '18 at 21:23
Then please make a reproducible example. http://sqlfiddle.com would be a great help. — revo, Jun 17 '18 at 21:26
Actually It is only one question: How to use only day and month to make queries by period. — Guilhermy, Jun 17 '18 at 22:22
Create a new table field with `DATE` type, where you save birthdays with same year for all records. Date calculation would be much easier + field index could be used... — Glavić, Jun 18 '18 at 07:05
@Glavić I have more than 1k users on my database, this solution could be great, but it is no longer viable. — Guilhermy, Jun 18 '18 at 15:59
@GuilhermyCamargo: 1k is nothing :) You don't even need indexes for that :) Just use DATE_FORMAT() or SUBSTRING() :)) — Glavić, Jun 18 '18 at 16:02
@Glavić So you are suggesting me to add a new field or use these functions on my queries? — Guilhermy, Jun 18 '18 at 16:18
@revo I created the fiddle. http://www.sqlfiddle.com/#!9/1dec7a/5 — Guilhermy, Jun 18 '18 at 16:18
@GuilhermyCamargo: I will always recommend solution what will use indexes... — Glavić, Jun 18 '18 at 17:13
@Glavić I don't actually know how to use this. I had never used, and I didn't know until now. Could you elaborate some guide that corresponds to my problem? — Guilhermy, Jun 18 '18 at 17:34

How to search birthdays dates using only day and month

0 Answers0