Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2009
    Posts
    2

    Unanswered: SQL Date Query Problem

    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?

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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

  3. #3
    Join Date
    Apr 2009
    Posts
    2

    Month Part

    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?

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •