Re: efficient query for: "it's your birthday today"

[Santino]

Store month*100+day
 1999-03-01 --> 301
you can also say "it's your birthday today"  when 02/29 doesn't exists!
Santino
At 13:05 -0500 18-11-2004, [EMAIL PROTECTED] wrote:
Good idea! But, that method will fail for dates past Feb 29th on leap
years.
MYSQL>select dayofyear('1999-03-01'), Dayofyear('2000-03-01');
+-------------------------+-------------------------+
| dayofyear('1999-03-01') | Dayofyear('2000-03-01') |
+-------------------------+-------------------------+
|                      60 |                      61 |
+-------------------------+-------------------------+
Sorry!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Eric McGrane" <[EMAIL PROTECTED]> wrote on 11/18/2004 11:29:20 AM:
 How about adding another column that stores the day of year for the
 birthday.  You could then index on this column and your query would be
for
 11/7 would be
 doycol=DAYOFYEAR("2004-11-07")
 or
 doycol=312
 E
 ""Jigal van Hemert"" <[EMAIL PROTECTED]> wrote in message
 news:[EMAIL PROTECTED]
 > I have date of birth stored in a DATETIME column and need to find for
 which
 > persons a certain date is their birthday.
 >
 > I've tried so far:
 >
 >     DATE_FORMAT (col, '%m%d') = '1107'        => 0.2001 sec
 >
 >     col LIKE '%-11-07%'        => 0.1643 sec
 >
 >     col RLIKE '-11-07'        => 0.1702 sec
 >
 > Are there faster alternatives for MySQL 4.0.21 ?
 >
 > Regards, Jigal.
 >

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 > To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]
 >

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]