How to select all users who have a birthday in a certain period, excluding the year?

Question

MySQL

Googled hundreds of solutions on the Internet, but all of them have some nuances of work.
The main problem is that I need to select all users who have birthdays in a specified period, excluding the year.
For example, select everyone who has a DR between January 10 and January 25, no matter what year they were born.
Tried to stop at something like this, but it still doesn't work right.
SELECT `ID` FROM `users` WHERE DATE_FORMAT(`BIRTHDAY`,'%d.%m') BETWEEN '01.01' AND '19.01'
Selects users with DR:
01.01
05.01
16.01
And under this condition BETWEEN '01.02' AND '15.01'
, 01.05 and 16.01 still remain.

Can you please tell me how to do it right?

Answer 1

Maybe so? Those. the format is not DDMM but MMDD. But with a range, there will be problems with crossing the border of the year.

SELECT `ID` FROM `users` WHERE DATE_FORMAT(`BIRTHDAY`,'%c%d') BETWEEN '101' AND '119'

Answer 2

Can DAYOFYEAR be used?

SELECT `ID` FROM `users` WHERE DAYOFYEAR (`BIRTHDAY`) BETWEEN DAYOFYEAR ('01.01.1900') AND DAYOFYEAR ('19.01.1900')

Answer 3

I would generally advise not to do this, the query you provided should not work with the index, because you requested DATE_FORMAT for the data, which will lead to the processing of all expressions, make a separate field into which you throw the data as you need, you can use a trigger to fill in, and already search for it.

Answer 4

The sampling is complete, but the “reformat” of the date is applied here, which, with large volumes, will lead to a decrease in performance.
I would do it "classically", sampling taking into account the year from 1900 to ~ 3000 + there is no problem with the boundaries.

Answer 5

Make a column with the serial number of the day in the year for the birthday. Then you can make an index and the condition will be simple. Well, only, as indicated above, separately take into account the transition through the border of the year

Answer 6

Faced with similar ...
The problem was only that some records have a year and some do not.
We select only the month + day and set the current year. We set this variable as an index and filter it as we want.
I refused the DATE_FORMAT selects because the base is very large, and the selection by int index is many times faster.