Mysql Birthday query for all years

Question

I have a customers table and I would like to filter all the customers whose birthday is between a given date range, let's say January 1st and March 15th. I've been trying to use:

(DAY(birthday) >= 01 AND MONTH(birthday) >= 01) AND (DAY(birthday) <= 15 AND MONTH(birthday) <= 03)

but this will return only the ones whose birthday is between day 1 and 15. 1 to 15 of January, 1 to 15 of February and 1 to 15 of March. Isn't there a way to do this:

birthday BETWEEN '%-01-01' AND '%-03-15'

Thanks in advance

Does this answer your question? [Date between dates, ignore year](https://stackoverflow.com/questions/25597491/date-between-dates-ignore-year) — Coloured Panda, Jan 26 '23 at 10:28

score 3 · Answer 1 · edited Nov 02 '17 at 12:26

3

You could use CASE with WHERE, e.g.:

WHERE
  CASE 
    WHEN MONTH(birthday) BETWEEN 1 AND 2 THEN DAY(birthday) BETWEEN 1 AND 31
    WHEN MONTH(birthday) = 3 THEN DAY(birthday) BETWEEN 1 AND 15
    ELSE FALSE
  END

edited Nov 02 '17 at 12:26

Strawberry

33,750
13
40
57

answered Nov 02 '17 at 12:12

Darshan Mehta

30,102
11
68
102

score 1 · Accepted Answer · answered Nov 02 '17 at 12:21

1

You could also use DayOfYear to get the day number in a year, so 1 jan is day 1. And 1 aug is day 213.

This wil ignore the year completely.

SELECT * 
FROM `table` 
WHERE DayOfYear(`birthday`)
BETWEEN DayOfYear('01-01-01') AND DayOfYear('2200-03-15')

Found: Date between dates, ignore year

answered Nov 02 '17 at 12:21

Ron van der Heijden

14,803
7
58
82

Aug 1 will be Day 214 on leap years. Birthdays are celebrated on the same date usually, irrelevant if its a leap year or not. year 2200 is not a leap year, btw. – Coloured Panda Jan 26 '23 at 10:28

Mysql Birthday query for all years

2 Answers2