Yup, very good point. I am the one who is sorry. My bad.
E
________________________________
From: SGreen@stripped [mailto:SGreen@stripped]
Sent: Thursday, November 18, 2004 1:06 PM
To: McGrane, Eric
Cc: mysql@stripped
Subject: Re: efficient query for: "it's your birthday today"
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
>
