MySQL Forums
Forum List  »  Newbie

Re: Retrieve list of users who had birthday in last 7 days
Posted by: Felix Geerinckx
Date: August 05, 2005 06:44AM

Jay Pipes wrote:

> SELECT name, birthdate
> FROM my_table
> WHERE WEEK(birthdate) = WEEK(CURRENT_DATE());

This will not always work: e.g.

mysql> SELECT WEEK('2005-04-24') = WEEK('1962-04-21');
+-----------------------------------------+
| WEEK('2005-04-24') = WEEK('1962-04-21') |
+-----------------------------------------+
| 0 |
+-----------------------------------------+

I suggest:

SELECT
name, birthdate
FROM my_table
WHERE
YEAR(FROM_DAYS(TO_DAYS(CURRENT_DATE)-TO_DAYS(birthdate)+1)) =
YEAR(FROM_DAYS(TO_DAYS(CURRENT_DATE)-TO_DAYS(ADDDATE(birthdate, INTERVAL 7 DAY))+1))+1;

--
felix
Please use BBCode to format your messages in this forum.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.