SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Evangelist lance_vincent's Avatar
    Join Date
    Aug 2004
    Location
    philippines
    Posts
    574
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking birthdays in the next 7 days (was "Simple SQL Query")

    what if i have this bday field, and i want to return all records having bdays today and on the following seven days.. example, today is dec 1, i would like to display all those whose bday is dec 1-7. this should be simple, but im no good in sql funxtions, im using mysql..

    please help.. immediately,

    thanks guys!!
    If you won't dress like the
    Victoria Secret girls,
    don't expect us to act like soap opera guys.

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    where datediff(bdate, curdate()) between 0 and 7

  3. #3
    SitePoint Evangelist lance_vincent's Avatar
    Join Date
    Aug 2004
    Location
    philippines
    Posts
    574
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    will this work even on other dates, example, its november 28, it should return all bdays with nov28,29,30,dec1,2,3,4

    i hope you get me..
    If you won't dress like the
    Victoria Secret girls,
    don't expect us to act like soap opera guys.

  4. #4
    SitePoint Evangelist lance_vincent's Avatar
    Join Date
    Aug 2004
    Location
    philippines
    Posts
    574
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation

    what i wanted is something like this

    selet * from table where bday>=curdate and bday<=curdate+7

    i know its wrong, but its the idea...
    Last edited by lance_vincent; Nov 23, 2005 at 21:16. Reason: wrong var
    If you won't dress like the
    Victoria Secret girls,
    don't expect us to act like soap opera guys.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by longneck
    Code:
    where datediff(bdate, curdate()) between 0 and 7
    longneck, slow down and take another look at your solution

    you're returning rows where people's birthdates are either today or in the future

    um, last time i checked, a person whose birthdate is in the future hasn't been born yet, so it would be a very iffy situation to assign them a birthdate already

    here is my understanding of what lance actually wants:
    Code:
    select name
         , bdate
      from daTable
     where to_days(
             date_add(bdate,
                interval  year(current_date)
                         -year(bdate)    year )
                 )
         - to_days(current_date)
       between 0 and 7
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    garh! i forgot to take the year out!

    i'd smack my head against something, but when you have a twenty foot neck, your head can develop a lot of momentum with that backswing!

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by longneck
    i'd smack my head against something, but when you have a twenty foot neck, your head can develop a lot of momentum with that backswing!
    quote of the week
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Evangelist lance_vincent's Avatar
    Join Date
    Aug 2004
    Location
    philippines
    Posts
    574
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    wow... thanks guys... we were working on the first code and actually bumped in the same problem about the year...

    BTW, nice qoute...hehehe
    If you won't dress like the
    Victoria Secret girls,
    don't expect us to act like soap opera guys.

  9. #9
    SitePoint Member
    Join Date
    Mar 2005
    Location
    UA, Kharkov
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    correct solution:

    $days = 7;

    "(YEAR(DATE_ADD(CURDATE(), INTERVAL " . $days . " DAY)) - YEAR(BirthDate)) - (RIGHT(DATE_ADD(CURDATE(), INTERVAL " . $days . " DAY),5) < RIGHT(BirthDate,5)) > (YEAR(CURDATE())-YEAR(BirthDate)) - (RIGHT(CURDATE(),5) < RIGHT(BirthDate,5))"


Bookmarks

Posting Permissions

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