Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: Birthday Query

  1. #1
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98

    Question Unanswered: Birthday Query

    Hi, i have a table users which contains a birthday field, i would like to have a query that displays the users birthdays for the coming 7 days. Can someone help me with this.

    Thanx allot.
    Wimmo

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    select birthday,username from table where convert(datetime,convert(varchar(10),birthday,101) ,101) between convert(datetime,convert(varchar(10),getdate(),101 ),101) and convert(datetime,convert(varchar(10),dateadd(dd,7, getdate()),101),101)

    A very rough cut .. but i think it will work
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Dec 2003
    Location
    Delhi INDIA
    Posts
    58
    if this column is datetime, then you can use difference of getdate and datediff function for wanted result.
    Deep..

    MCDBA
    ------------------------------------------
    How much data you can afford to lose??

  4. #4
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98
    Hi Enigma thanx for your reply, your query works but only when the year of birth is the current year

  5. #5
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    My Bad ...
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  6. #6
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    http://www.dbforums.com/showthread.php?threadid=985381 for a similar thread and reference.

    HTH
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  7. #7
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98
    Quote Originally Posted by Satya
    http://www.dbforums.com/showthread.php?threadid=985381 for a similar thread and reference.

    HTH
    I tried that solution but it didn't work for me

  8. #8
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98
    How can i change your query so that it dont look @ the year but only to the day & month

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    WHERE DATEDIFF(dd,GetDate(),Birthdate) <= 7?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Ooopps....

    Code:
    DECLARE @x datetime, @y datetime
    SELECT @x = GetDate(), @y = '05/6/2004'
       SET @x = '1900/'+CONVERT(varchar(2),DATEPART(mm,@x))+'/'+CONVERT(varchar(2),DATEPART(dd,@x))
       SET @y = '1900/'+CONVERT(varchar(2),DATEPART(mm,@y))+'/'+CONVERT(varchar(2),DATEPART(dd,@y))
    
    SELECT DATEDIFF(dd,@x,@y)
    Did you look at the function?

    That should definetly work for you....
    Last edited by Brett Kaiser; 04-28-04 at 10:10.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98
    Thanx for your response but i do not completely understand what you are saying here

  12. #12
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98
    I am trying to get the function work but i do not know what i am doing wrong

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Try using:
    PHP Code:
    --  ptp  20040427  Compute days to next anniversary

    CREATE 
    FUNCTION dbo.fDaysToAnniversary(
       @
    pdAnn        DATETIME    -- Base Date
    ,  @pdNow        DATETIME    -- Target dateusually today
    RETURNS SMALLINT AS BEGIN

    DECLARE @dNext        DATETIME    -- Next anniversary

    SET 
    @dNext DateAdd(yearDateDiff(year, @pdAnn, @pdNow), @pdAnn)    -- First guess
    IF @dNext < @pdNow SET @dNext DateAdd(year1, @dNext)        -- Bump if already past

    RETURN DateDiff(day, @pdNow, @dNext)
    END
    GO 
    This function takes two arguments, one is a date you wish to project an anniversary for, and the other is the date you want to figure the days from. You'd typically use it something like:
    PHP Code:
    SELECT dbo.fDaysToAnniversary('2001-09-11'GetDate()) 
    -PatP

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Cut and paste this...I think there was a type-o in the original code...should execute no proble in QA

    Code:
    USE Northwind
    GO
    
    CREATE FUNCTION udf_DaysToBDay(@Bday datetime, @Dateuntil datetime)
    RETURNS int
    AS
      BEGIN
    	DECLARE @x int, @ModifyBDay datetime
    
    	SET @ModifyBDay = CONVERT(datetime,
    			CONVERT(char(4),year(@Dateuntil)) 
    			+ '/' 
    			+ CONVERT(char(5),@Bday,101) 
    			) 
    
    	SET @x = datediff(dd, @Dateuntil, @ModifyBDay)
    
    	RETURN @x
      END
    
    GO
    
    SELECT dbo.udf_DaysToBDay('10/24/1960',GetDate())
    
    SELECT dbo.udf_DaysToBDay(BirthDate,GetDate()) FROM Employees
    GO
    
    DROP FUNCTION udf_DaysToBDay
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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