Hello.
Why don't you want to use just:
select id from members where birthday = now();
See:
mysql> show create table members\G;
*************************** 1. row ***************************
Table: members
Create Table: CREATE TABLE `members` (
`id` int(11) NOT NULL auto_increment,
`birthdate` date default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> select * from members;
+----+------------+
| id | birthdate |
+----+------------+
| 1 | 2004-02-29 |
| 2 | 2005-02-28 |
| 3 | 2005-08-24 |
+----+------------+
mysql> select id from members where birthdate = now();
+----+
| id |
+----+
| 3 |
+----+
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2005-08-24 20:27:20 |
+---------------------+
1 row in set (0.00 sec)
mysql> select version();
+-----------------------+
| version() |
+-----------------------+
| 5.0.11-beta-debug-log |
+-----------------------+
1 row in set (0.00 sec)
If you add an index on birthdate, you can avoid table scans.
create index `birthdate` on members(birthdate);
mysql> explain select id from members where birthdate = now()\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: members
type: ref
possible_keys: birthdate
key: birthdate
key_len: 4
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
Pooly <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I would like to display a list of members who have their birthday a
> given day (today for instance).
> My idea is to store their birth date in a column, and then query the
> table against the column. But the query would be like :
> select id from members where MONTH(birthday) =3D MONTH(NOW()) AND
> DAY(birthday)=3DDAY(NOW())
> but it would perform a entire table scan with that.
> What would be your best strategy for that sort of query ?
> And how would you deal with 29th of february ?
>
> --=20
> Pooly
> Webzine Rock : http://www.w-fenec.org/
>
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
