Select between dates, but ignoring year. Problems when filtering over year end

user_9229931 0
Viewed 47

I have a table with birthdays:

CREATE TABLE birthdays
    (`name` varchar(10), `day` datetime)
;
    
INSERT INTO birthdays
    (`name`, `day`)
VALUES
    ('Joe', '2000-03-18'),
    ('Mike', '2000-12-23'),
    ('Albert', '2000-02-15');

I want to find all person with birthday inside a given interval. It works fine, when my two dates are in the same year:

SELECT * 
FROM birthdays 
WHERE DATE_FORMAT(day, "%m-%d") BETWEEN DATE_FORMAT('2021-12-01',"%m-%d") AND DATE_FORMAT('2021-12-31',"%m-%d");

But the moment I filter between two dates over year end, I get nothing. I tried two approaches: DATE_FORMAR and DAYOFYEAR, with the same result:

SELECT * 
FROM birthdays 
WHERE DATE_FORMAT(day, "%m-%d") BETWEEN DATE_FORMAT('2021-11-28',"%m-%d") AND DATE_FORMAT('2022-01-01',"%m-%d");

SELECT * 
FROM birthdays
WHERE DAYOFYEAR(day) BETWEEN DAYOFYEAR('2021-11-28') AND DAYOFYEAR('2022-01-01')

Here is a SQL Fiddle with the problem.

mysql
2 Answers
SET @a='2021-11-28';
SET @b='2022-01-01';

SELECT * FROM birthdays 
WHERE CASE 
  WHEN DATE_FORMAT(@a, '%m-%d') < DATE_FORMAT(@b, '%m-%d') 
  THEN DATE_FORMAT(day, '%m-%d') BETWEEN DATE_FORMAT(@a, '%m-%d') AND DATE_FORMAT(@b, '%m-%d') 
  ELSE DATE_FORMAT(day, '%m-%d') < DATE_FORMAT(@b, '%m-%d') 
    OR DATE_FORMAT(day, '%m-%d') > DATE_FORMAT(@a, '%m-%d') 
  END;

Output:

+------+---------------------+
| name | day                 |
+------+---------------------+
| Mike | 2000-12-23 00:00:00 |
+------+---------------------+
Bill_Karwin_208600

I have always used the method shown above by Bill but it occurred to me that there must be a simple way to do the same with a recursive CTE, which would avoid all the date functions and allow index usage.

This is MySQL >= 8.0 and will only be of benefit for larger datasets -

WITH RECURSIVE `date_range` (`from_date`, `to_date`) AS (
    -- set from_date and to_date
    SELECT '2021-11-28', '2022-01-01'
    UNION ALL
    SELECT  `from_date` - INTERVAL 1 YEAR, `to_date` - INTERVAL 1 YEAR
    FROM    `date_range`   
    -- set earliest value for from_date
    WHERE   `from_date` - INTERVAL 1 YEAR >= '1920-01-01'
)
SELECT b.*
FROM `date_range`
INNER JOIN birthdays b ON b.day BETWEEN `date_range`.`from_date` AND `date_range`.`to_date`

MySQL Recursive Common Table Expressions

Example of date series generation

user_11912470
Related