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, SGreen@stripped 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" <emcgrane@stripped> 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"" <jigal@stripped> wrote in message
>> news:006801c4cd50$ea60b2a0$2801a8c0@localdomain...
>> > 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/mysql?unsub=1
> >
