> 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
____ • 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.
