Re: pick next birthday

[Bob Hall <rjhalljr@xxxxxxxxxxxxx>]

> This MAY be a duplicate.  I posted through Deja.com but I am not 
> sure if mail gets here from that method.
>
> I need to have a table that has (among others) a field that contains 
> a birthdate. I want to select the record from the table that has 
> todays date as the birthday or (if none matches today) the next 
> upcoming birthday. What I want to display is this.
>
> If someone has a birthday today:
> A message about Happy Birthday and the persons age.
>
> If no birthday today:
> A message saying who's birthday is next and how old they will be.
>
>
> There will be NO duplicate birthdates.

SELECT If(
          Substring(birthdate, 6) >= substring(Curdate(), 6),
          Concat(Extract(YEAR FROM Curdate(), Substring(birthdate, 5)),
          Concat(1 + Extract(YEAR FROM Curdate()), Substring(birthdate, 5))
       ) AS next_bday,
       If(
          Substring(birthdate, 6) >= substring(Curdate(), 6),
          Extract(YEAR FROM Curdate()) - Extract(YEAR FROM birthdate),
          (1 + Extract(YEAR FROM Curdate())) - Extract(YEAR FROM birthdate)
       ) AS next_age
FROM <table name>
ORDER BY next_bdate LIMIT 1;

This only works if there are no duplicate *birthdays*. If two 
*birthdates* differ only in the year, this will only return one. To 
get around that, SELECT next_bday into a temp table, and then SELECT 
all the birthdays that match on day and month. Calculate next_age in 
the second SELECT. I'd give an example, but I'm out of time.

Bob Hall

Know thyself? Absurd direction!
Bubbles bear no introspection.     -Khushhal Khan Khatak

--
---------------------------------------------------------------------
Please check "http://www.mysql.com/documentation/manual.php"; before
posting. To request this thread, e-mail mysql-thread60382@xxxxxxxxxxxxxxx

To unsubscribe, send a message to:
   <mysql-unsubscribe-treed=ultraviolet.org@xxxxxxxxxxxxxxx>

If you have a broken mail client that cannot send a message to
the above address (Microsoft Outlook), you can use:
   http://lists.mysql.com/php/unsubscribe.php