SQL Date Query Problem

pofigster · #1 (**permalink**) 04-27-09, 10:56

Howdy, I have a question on how to do a more variable date query than I can currently do.

I have a database of people which includes information such as their birthday. On my site I want to have a little panel which displays who has a birthday today, sometime in the next week and sometime in the next month.

The birthday variable is stored as a 'date' (YYYY-MM-DD).

Now, for the birthdays "today" my query looks like:

Code:

SELECT * FROM people WHERE month(birth) = '$tm' AND day(birth) = '$td'

where $tm and $td are today's month and day, respectively.

My problem arises when I'm trying to do my over the next week and month query. If I do:

Code:

SELECT * FROM people WHERE (month(birth) BETWEEN '$tm' AND '$wm') AND (day(birth) BETWEEN '$td' AND '$wd')

where $wm and $wd are the month and day of a week from today, respectively - if I do it like this then when it wraps around to a new month it'll check early days in the first month and later days in the second month.

Is there some way to do a query for a date like:

Code:

SELECT * FROM people WHERE birth BETWEEN '????-MM-DD' AND '????-MM-DD'

or somehow get MySQL to ignore the year attached to the date?

mike_bike_kite · #2 (**permalink**) 04-27-09, 11:47

This should work for users who's birthday is this month

Code:

select * 
from   people
where  date_format( now(),"%m" ) = date_format( birth,"%m" )

Then change the %m to %u for birthdays this week. There's also a %U option if you want the week to start on sun rather than mon.

Mike

pofigster · #3 (**permalink**) 04-27-09, 13:20

Mike,

Thanks for your quick response. Let me clarify what I want to do. I don't want to look for dates in the calendar week or month, I want to look for dates over the next 7 days and the next 30-31 days. So, my BETWEEN statement would be (for the next week)

Code:

SELECT * FROM people WHERE birth BETWEEN '????-04-28' AND '????-05-04'

That way anybody selected for today wouldn't appear in the "next week" slot and then for the month it would be:

Code:

SELECT * FROM people WHERE birth BETWEEN '????-05-05' AND '????-05-27'

Because I can't include the year. Is what I'm trying to do possible?

mike_bike_kite · #4 (**permalink**) 04-27-09, 15:57

Quote:

Originally Posted by pofigster

Thanks for your quick response. Let me clarify what I want to do. I don't want to look for dates in the calendar week or month, I want to look for dates over the next 7 days and the next 30-31 days. So, my BETWEEN statement would be (for the next week)

I think what I gave you does that. You wanted to find who's birthday was in the current month by finding out the start and end dates of the month and then seeing if anybodies birthday fell between those two dates but in any year. While I just pulled the month from todays date and then compared it to the month of the user's birthday.

If I've got the wrong end of the stick then please supply a clear example with dates so I can understand better.

Mike

Internet Services

Web Development

Digital Marketing

Consumer Tech