Page 1 of 3 123 LastLast
Results 1 to 15 of 38
  1. #1
    Join Date
    Feb 2004
    Posts
    3

    Exclamation Unanswered: List up-coming birthdays

    I've got an employee table with a date of birth field in it. i need a query that will a allow me to list all employees who's birthdays are coming up the next 30 days (or 1 month, if easier). I've tried several approaches & am getting nowhere... Any help would be greatly appreciated.

    Regards,
    Jacques Matthee.

  2. #2
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: List up-coming birthdays

    SELECT *
    FROM Employes
    WHERE DATEDIFF(day, birthday, getdate())<=30

  3. #3
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: List up-coming birthdays

    oups !

    that needs a little reajustment
    on moment please...

  4. #4
    Join Date
    Feb 2004
    Posts
    3

    Re: List up-coming birthdays

    Thanks for the quick reply! i'll give it a bash!

    Originally posted by Karolyn
    SELECT *
    FROM Employes
    WHERE DATEDIFF(day, birthday, getdate())<=30

  5. #5
    Join Date
    Feb 2004
    Posts
    3

    Re: List up-coming birthdays

    now it returns everything, because the birthdays are in the 1970s & 80s compared to the current year which is 2004.

    Originally posted by jacmat
    Thanks for the quick reply! i'll give it a bash!

  6. #6
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: List up-coming birthdays

    it doesn't work
    I'm creating a function for you
    that you'll able to use the get the good results

  7. #7
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: List up-coming birthdays

    just a few minutes ...

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    PHP Code:
    select id
         
    name
         
    dob
      from employees
     where datediff
    (dd
             
    getdate()  
             , 
    cast(convert(char(4),year(getdate()) & '/' 
                  
    convert(char(5),dob,101)
               as 
    datetime)
             ) <= 
    30 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: List up-coming birthdays

    create this function


    CREATE function dbo.DatePart(@Date Varchar(26), @Format VarChar(20))
    returns Varchar(10) as
    begin

    return( (case @Format
    when 'YYYY' then Convert(char(4), Year(@Date))
    when 'MM' then Replicate('0', Len(Cast(Month(@Date) as char(2)))) + Cast(Month(@Date) as char(2))
    when 'DD' then Replicate('0', Len(Cast(Day(@Date) as char(2)))) + Cast(Day(@Date) as char(2))
    end))
    end

  10. #10
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: List up-coming birthdays

    SELECT * FROM Employes
    Where
    DateDiff(day,
    Cast(
    RTrim(dbo.DatePart(getdate(), 'YYYY')) + '-' +
    RTrim(dbo.DatePart('2002-01-02','DD')) + '-' +
    RTrim(dbo.DatePart('2002-01-02','MM')) as datetime),getdate())<=30

  11. #11
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: List up-coming birthdays

    r937 solution's is quite is simpler...

  12. #12
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: List up-coming birthdays

    I always forget the convert format number (like 101)


    very practical...
    I'll remember this

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Karolyn, you have to put getdate() as the 2nd parameter in DATEDIFF because you want birthdays 30 days after today, not 30 days before
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    a soooooo little detail

    or put -30

  15. #15
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    hey, R937 can you answer my concat null option question ???
    (posted recently)

Posting Permissions

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