/me bows down and cowers before the Almighty SQL God (a.k.a rudy) ;)
Matt On Fri, 1 Jul 2005 08:54:12 -0400 "rudy" <[EMAIL PROTECTED]> wrote: > > > 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. ____ 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: unknown lmsubst tag argument: '' 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.
