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.
CodePudding user response:
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 |
------ ---------------------
CodePudding user response:
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`