|
|
Posted by Lars Eighner on 06/23/07 20:37
In our last episode, <slrnf7qtg2.25ri.usenet@goodwill.larseighner.com>, the
lovely and talented Lars Eighner broadcast on comp.lang.php:
> In our last episode, <f39fi.2768$ZA.1266@newsb.telia.net>, the lovely and
> talented Bo Wisén broadcast on comp.lang.php:
>> In a MYSQL database I have stored when the members were born in the form
>> "YYYYMMDD". Now I want a method to select every member who has birthday from
>> today and up to 30 days into the future. I would prefer if this could be
>> done in a MYSQL query.
> Well then, this would seem to be a MySQL question. See 'Date and Time
> Functions' in the MySQL Manual.
>> Can anyone tell me how to do this? I have figured out some solutions that
>> will work, but unfortunately not in December because the year will turn.
Okay, I give up. You made me think about this enough to figure it out.
I'm going to express this in words, you look up the MySQL functions
1. Find the difference between today and subject's birthday this year.
Compose subject's birthday this year by extracting the year from the
current date and the month and day from the subjects birthdate.
DATEDIFF will return the difference in days.
2. Devise the test that will work before December. That would be
if the date difference is equal or greater than zero AND less than
31 (hits on today and today + 30 days, but not today +31 days)
However this misses when today is after Dec. 1 and subjects birthday is
before Jan. 30. So this test had to be ORed with another test.
2. Devise the rest of the test.
Find the number of days in this year. (This is to account for the
possibility that this is a leap year). Do this by finding out what is the
day number of Dec 31 this year.
Subtract 30 from this (could be off by one, check in testing).
Negate the result.
so the ORed test should be date diff in days < result just obtained.
--
Lars Eighner <http://larseighner.com/> <http://myspace.com/larseighner>
Countdown: 576 days to go.
Owing to googlegroups not screening users to eliminate spammers and other
USENET abusers, I do not see most posts from googlegroups.
Navigation:
[Reply to this message]
|