On Wed, May 02, 2001 at 04:00:17PM +0100, Roo wrote:
> Hi all,
>
> I was recently attempting what I thought would be an easy search. I
> wanted to return all records with a birthdate (a DATE column) 'within'
> 30 days of the current date.
>
> The birthdate is stored as a YYYY-MM-DD, I have been having lots of
> trouble getting MySQL to return the correct records, my problems are..
>
> 1.I need to ignore the year value as all birthdates will be previous
> to the current if the year is included. 2.I need to check if a
> birthdate is in the same month as the current month and that its day
> is not less than the current day. 3.Then I need to check how many
> days there are left in the current month, take it away from 30 and
> then check for birthdates in the following month within that many
> days.
>
> So much for being a simple problem!
>
> Anyone have any experience of doing this..?
>
> LINUX..MySQL 3.22.32 on apache
>
> thanks a bunch for any info
You're making it much too complicated. Try:
select birthdate from table
where concat(substring(now(),1,4),substring(birthdate,5,6))
between date_sub(now(), interval 30 day)
and date_add(now(), interval 30 day)
Much to my surprise this did not fail when there were birthdays
on Feb. 29.
--
"The action required to sustain human life is primarily intellectual:
everything man needs has to be discovered by his mind & produced by
his effort... Since knowledge, thinking, & rational action are
properties of the individual, since the choice to exercise his
rational faculty or not depends on the individual, man's survival
requires that those who think be free of the interference of those who
don't. Since men are neither omniscient nor infallible, they must be
free to agree or disagree, to cooperate or to pursue their own
independent course, each according to his own rational judgment.
Freedom is the fundamental requirement of man's mind."
-- Ayn Rand
Rick Pasotto email: [EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
