>
> > This is on MySQL with php (both local and the host) and at the moment I
have
> > Date as the field type as I have no use for the time of day (I'm not
> > offering horoscopes).
>
> excellent
>
> so, let's say that you were interested in pulling out everybody from your
> database who has a birthday coming up in the next 10 days
>
> the first thing you need to realize is that the solution will not involve
> comparing the month and day of the birthday to the month and day of today
>
> this might have been your thinking process in wanting to store separate
year,
> month, and day columns -- but all you would end up doing is having to
combine
> them again so that you can do date arithmetic, because you sure as heck
don't
> want to set up the if/then/else logic (it's a real dog's breakfast) for
handling
> date ranges over month end boundaries
>
> i'll walk you through it in detail
>
> STEP 1
>
> consider the difference in years between today and your birthday
>
> if you were born earlier in the year than today (e.g. 1969-02-28) then the
> difference between 2005 and 1969 is 36
>
> but if you were born later in the year than today (e.g. 1969-09-17) then
the
> difference between 2005 and 1969 is 36
>
> this might be obvious, but i broke it down just in case ;o)
>
> so, what you do is add the difference back to your birthday
>
> 1969-02-28 + 36 years = 2005-02-28
>
> 1969-09-17 + 36 years = 2005-09-17
>
> this "moves" your birthday into the current year
>
> STEP 2
>
> compare the "moved" birthday to today by calculating the difference in
days
>
> if the difference is between 0 and 10, the person has a birthday coming up
in
> the next 10 days
>
> so here is the query --
>
> select name, birthdate
> from yourtable
> where datediff(
> date_add(birthdate
> , interval year(current_date)
> -year(birthdate) year )
> , current_date )
> between 0 and 10
>
> the reason it's "between 0 and 10" instead of "< 10" is because you will
get
> negative results from the DATEDIFF
>
> oh, by the way, DATEDIFF works only in 4.1.1, so if you're on an earlier
> version, there's another way to do it, using the TO_DAYS() function twice
>
> okay, now for the final wrinkle
>
> STEP 3
>
> if a person's birthday is january 3rd and today is december 28th, the
above
> query will not work -- remember, we "moved" the birthday into the current
year,
> so the difference would be a large negative number
>
> so what we have to do is "move" the birthday into the next year, whenever
the
> birthday occurs earlier in the year than today
>
> we could do this in STEP 1, by adding 1 to the number of years for those
> situations, but this would require an "if" test
>
> it's easier just to throw the extra test into the WHERE clause
>
> select name, birthdate
> from yourtable
> where datediff(
> date_add(birthdate
> , interval year(current_date)
> -year(birthdate) year )
> , current_date )
> between 0 and 10
> or datediff(
> date_add(birthdate
> , interval year(current_date) + 1
> -year(birthdate) year )
> , current_date )
> between 0 and 10
>
> final thoughts --
>
> you might be wondering why it has to be so complex
>
> let me assure you, the complexity arises *not* from the fact that you have
> chosen to implement birthdates in DATE format
>
> it derives entirely from the complexity of our calendar
>
> remember, month-end and year-end boundaries are tricky, and remain so no
matter
> which database you're using
>
> however, the built-in date functions that each database has will minimize
the
> complexity
>
> feel free to imagine the additional complexity that you would need if you
stored
> your birthdates either as unix timestamps (ptui!) or separate year, month,
and
> day columns
>
>
> rudy
rudy,
To my relief I pretty well follow what you are saying (I only want to pull
out one day's celebrants each time so I reckon I would leave off 'between 0
and 10'). And I do thank you for your patience.
While I am not pretending to know better (obviously) I wonder why it is not
possible - perhaps as 'int' fields to have this:
Year Month Day
1936 08 25
1982 10 3
etc
from pull down tables and insert numerical equivalents for the month.
and then simply query Where month=10 and day=3.
I don't want to pull the date out, only the email address and names for only
for one day at a time so this would give me a query something like:
SELECT email_address FROM loop_email_addresses AND first_n, last_n FROM
loop_allname
WHERE emailID=(SELECT nameID FROM loop_email
WHERE nameID=(SELECT month(10) AND birth_day(3) FROM
loop_birth));
The name table will receive data from other forms and the email addresses
table similarly
My reason for thinking this might be my path is that my needs here are very
simple and the birth data would not be used for anything else (if the site
continues for long enough the year would be useful for 'now you are 21',
'now you are 40', 'now you are as decrepit as me' kind of thing).
Joseph
____ • The WDVL Discussion List from WDVL.COM • ____
To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or
use the web interface http://e-newsletters.internet.com/discussionlists.html/
Send Your Posts To: [email protected]
To change subscription settings, add a password or view the web interface:
http://intm-dl.sparklist.com/read/?forum=wdvltalk
________________ http://www.wdvl.com _______________________
You are currently subscribed to wdvltalk as: [email protected]
To unsubscribe send a blank email to [EMAIL PROTECTED]
To unsubscribe via postal mail, please contact us at:
Jupitermedia Corp.
Attn: Discussion List Management
475 Park Avenue South
New York, NY 10016
Please include the email address which you have been contacted with.
