Reputation: 1075
I am trying to write a PHP script that queries a MySql database and returns the entries for which the Birthday date field is within the next 30 days. I am able to do this, but I need to exclude the year from the Birthday date when making the query. For example, if today's date is 7/10/2013, and the birthday in question is 7/13/1991, obviously these two dates are more than 30 days apart, but if we exclude the year, they are only 3 days apart. Making this a valid Birthday to return.
The Birthday field is stored as a DATE in the MySql database.
The query I have made so far:
$results = mysql_query("SELECT * FROM Club WHERE Birthday < DATE_SUB(NOW(), INTERVAL 30 DAY)");
What can I do to modify this query to not take the year into account?
Upvotes: 3
Views: 3600
Reputation: 5377
This was a little tricky, but here is my solution:
SELECT
birthday,
-- Shift the birthday into the current year
CAST(CONCAT(YEAR(NOW()), '-', MONTH(birthday), '-', DAY(birthday)) AS DATE) AS BirthdayInCurrentYear
FROM Club
WHERE CAST(CONCAT(YEAR(NOW()), '-', MONTH(birthday), '-', DAY(birthday)) AS DATE)
BETWEEN NOW() AND NOW() + INTERVAL 30 DAY;
This query works with leap years and with dates in the following year. You can test and verify using my SQLFiddle.
This might not be the fastest solution as it creates a VARCHAR and parses it back to a DATETIME. Unfortunately I did not find a better way the set only the year part of a DATETIME.
Upvotes: 0
Reputation: 24970
set up your peeps, determine their next birthdate (can be in next calendar year), display the ones having a birthday in next 30 days. works at end of year wrapper (in december), etc
http://sqlfiddle.com/#!2/d881b/4
create table peeps
( id int not null auto_increment,
lastname varchar(40) not null,
birthdate datetime not null,
primary key (id)
);
insert peeps (lastname,birthdate) values ('sam', '1973-01-01');
insert peeps (lastname,birthdate) values ('julie', '1973-02-01');
insert peeps (lastname,birthdate) values ('kim', '1973-03-01');
insert peeps (lastname,birthdate) values ('fred', '1973-04-01');
insert peeps (lastname,birthdate) values ('oscar1', '1973-05-01');
insert peeps (lastname,birthdate) values ('oscar2', '1973-05-02');
insert peeps (lastname,birthdate) values ('oscar3', '1973-05-04');
insert peeps (lastname,birthdate) values ('oscar4', '1973-05-06');
insert peeps (lastname,birthdate) values ('oscar5', '1973-05-08');
insert peeps (lastname,birthdate) values ('oscar6', '1973-05-10');
insert peeps (lastname,birthdate) values ('oscar7', '1973-05-12');
insert peeps (lastname,birthdate) values ('oscar8', '1973-05-14');
insert peeps (lastname,birthdate) values ('oscar9', '1973-05-16');
insert peeps (lastname,birthdate) values ('oscar10', '1973-05-18');
insert peeps (lastname,birthdate) values ('oscar11', '1973-05-20');
insert peeps (lastname,birthdate) values ('oscar12', '1973-05-22');
insert peeps (lastname,birthdate) values ('oscar13', '1973-05-24');
insert peeps (lastname,birthdate) values ('Felix the Cat1', '1973-06-01');
insert peeps (lastname,birthdate) values ('Felix the Cat2', '1973-06-05');
insert peeps (lastname,birthdate) values ('Felix the Cat3', '1973-06-07');
insert peeps (lastname,birthdate) values ('Bonehead7', '1973-07-01');
insert peeps (lastname,birthdate) values ('Bonehead8', '1973-08-01');
insert peeps (lastname,birthdate) values ('Bonehead9', '1973-09-01');
insert peeps (lastname,birthdate) values ('Bonehead10', '1973-10-01');
insert peeps (lastname,birthdate) values ('Bonehead11', '1973-11-01');
insert peeps (lastname,birthdate) values ('Bonehead12', '1973-12-01');
create table peeps_next_birthday
(id int not null,
next_birthdate datetime not null
);
insert into peeps_next_birthday (id,next_birthdate) select id,birthdate from peeps;
UPDATE peeps_next_birthday set next_birthdate=date_add(next_birthdate, interval (year(curdate())-year(next_birthdate)) year);
update peeps_next_birthday set next_birthdate=date_add(next_birthdate,interval 1 year)
where curdate()>next_birthdate;
/// *************************** now show the birthdays in the coming 30 days
select t2.id,t2.lastname,t1.next_birthdate
from peeps_next_birthday t1
join peeps t2
on t2.id=t1.id
where datediff(t1.next_birthdate,curdate())<=30
Upvotes: 1
Reputation: 51
Is this what you're looking for?
SELECT * FROM Club WHERE month(Birthday) = month(DATE_SUB(NOW(), INTERVAL 30 DAY))
and dayofmonth(Birthday) = dayofmonth(DATE_SUB(NOW(), INTERVAL 30 DAY));
Upvotes: 1
Reputation: 14169
I haven't tested it but I think that this will do and from reading it is clear what it does.
SELECT * FROM Club WHERE DAYOFYEAR(Birthday) BETWEEN DAYOFYEAR(NOW()) AND DAYOFYEAR(NOW()) + 30;
Upvotes: -1