CODEWITHSUNDEEP
phpmysqldate
NSNolan
NSNolan

Reputation: 1075

Query MySql Database for entries with 'Birthday' date field within 30 days from current date

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

Answers (4)

CodeZombie
CodeZombie

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

Drew
Drew

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

Ryan Kazinec
Ryan Kazinec

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));

http://pastebin.com/uEXrXrHT

Upvotes: 1

James C
James C

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

Related Questions