Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Oct 2008
    Posts
    24

    Unanswered: How to I return a birthday date in a query

    If I wanted to return a query of upcoming birthdays for as much as 30 days from now (get() date?) It is for the table customer and the date format is mm/dd/yyyy.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT birth_date
         , surname
         , forename
         , customer_id
    FROM   dbo.customers
    WHERE  birth_date BETWEEN GetDate() AND DateAdd(dd, 30, GetDate())
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Note that GetDate() returns the time portion also, so you may wish to truncate this off so you grab everything for today.
    Code:
    DECLARE @today datetime
        SET @today = DateAdd(dd, DateDiff(dd, 0, GetDate()), 0)
    
    SELECT birth_date
         , surname
         , forename
         , customer_id
    FROM   dbo.customers
    WHERE  birth_date BETWEEN @today AND DateAdd(dd, 30, @today)
    George
    Home | Blog

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm guess he wants recurring anniversary dates, not actual birthdates....
    These user-defined functions are the best method I have come up with:
    Code:
    create function [dbo].[LastAnniversaryAsOf](@Anniversary datetime, @AsOfDate datetime)
    returns datetime as
    begin
    /*
    ------------------------------------------------------------------------------------------------------------------------
    FUNCTION:	LastAnniversaryAsOf
    DESCRIPTION:	Returns the last anniversary as of a specified date.
    ------------------------------------------------------------------------------------------------------------------------
    REVISION HISTORY:
    EDITOR		DATE		REVISIONS
    blindman	10/3/2007	Function created
    ------------------------------------------------------------------------------------------------------------------------
    */
    
    --Test parameters
    --declare	@Anniversary datetime
    --declare	@AsOfDate datetime
    --set	@Anniversary = '6/10/1965'
    --set	@AsOfDate = getdate()
    
    return
    (select	max(Anniversary) as LastAnniversary
    from
    (select	dateadd(year, datediff(year, @Anniversary, @AsOfDate)-1, @Anniversary) as Anniversary
    UNION
    select	dateadd(year, datediff(year, @Anniversary, @AsOfDate), @Anniversary) as Anniversary) Candidates
    where	Anniversary < @AsOfDate)
    
    end
    go
    
    create function [dbo].[NextAnniversaryAsOf](@Anniversary datetime, @AsOfDate datetime)
    returns datetime as
    begin
    /*
    ------------------------------------------------------------------------------------------------------------------------
    FUNCTION:	NextAnniversaryAsOf
    DESCRIPTION:	Returns the Next anniversary as of a specified date.
    ------------------------------------------------------------------------------------------------------------------------
    REVISION HISTORY:
    EDITOR		DATE		REVISIONS
    blindman	10/3/2007	Function created
    ------------------------------------------------------------------------------------------------------------------------
    */
    
    --Test parameters
    --declare	@Anniversary datetime
    --declare	@AsOfDate datetime
    --set	@Anniversary = '6/10/1965'
    --set	@AsOfDate = getdate()
    
    return
    (select	min(Anniversary) as NextAnniversary
    from
    (select	dateadd(year, datediff(year, @Anniversary, @AsOfDate)+1, @Anniversary) as Anniversary
    UNION
    select	dateadd(year, datediff(year, @Anniversary, @AsOfDate), @Anniversary) as Anniversary) Candidates
    where	Anniversary > @AsOfDate)
    
    end
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Oct 2008
    Posts
    24
    yes, for example if I wanted to determine all the people who will be celebrating their birthday 30 days from now

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    WHERE birth_date BETWEEN @today AND DateAdd(dd, 30, @today)
    george, do you seriously expect a query to return any rows of people who are born between today and 30 days into the future? how can you be sure they won't be a couple days late?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by blindman
    These user-defined functions are the best method I have come up with
    Coo - I remember when you first wrestled with this:
    http://www.dbforums.com/showthread.php?t=985381&page=3
    This is my favourite but:
    Quote Originally Posted by blindman
    It will be my greatest honor to have my code engraved in the Book Of Limeback!
    So - these are your latest and best functions huh?

    My solution uses a tally table. Although not that efficient I wonder how it would perform against a scalar.....hmmm.
    Last edited by pootle flump; 11-17-08 at 04:35.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Lol, oops!
    That's what I get for trying to think at that time of night
    George
    Home | Blog

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I liked the idea of using a tally table, so here's a quick stab at it...
    Code:
    DECLARE @numbers table (
       n int
    )
    
    DECLARE @customers table (
       customer_id int identity(1,1)
     , birth_date  datetime
    )
    
    DECLARE @i int
        SET @i = 0
    
    SET NOCOUNT ON
    
      WHILE @i < 2000
        BEGIN
          INSERT INTO @customers (birth_date) VALUES (DateAdd(dd, @i, '19800101'))
      
          SET @i = @i + (10 * Rand())
        END
    
    SET NOCOUNT OFF
    
    INSERT INTO @numbers (n)
    SELECT Row_Number() OVER (ORDER BY c.n) As [n]
    FROM   (
            SELECT 0 As [n] UNION SELECT 1
           ) c
     CROSS
      JOIN syscolumns
    
    DECLARE @today datetime
        SET @today = DateAdd(dd, DateDiff(dd, 0, GetDate()), 0)
    
    SELECT c.*
    FROM   @customers c
     CROSS
      JOIN (
            SELECT n
            FROM   @numbers
            WHERE  n < 250
           ) n
    WHERE  DateAdd(yy, n.n, c.birth_date) BETWEEN @today AND DateAdd(dd, 30, @today)
    Obviously I'd replace the @numbers table variable with an indexed, "real" table.

    Is this anything close to waht you had Poots?
    George
    Home | Blog

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Broadly speaking, yes.

    I would make some changes, especially your accounting for people up to the age of 250. I like to code defensively too but no one will hit that age for a minimum of another 130 years! Each additional row from @numbers makes the solution that bit less efficient.

    It might also be worth separating the set up from the solution - many readers won't twig that much of the code is just setting up the tables & data.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually - there's no reason blindman's scalar functions can't be inline table functions, in which case I'd hazard a guess they would be more efficient than a tally table.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Yeah, I realise the 250 was slightly cumbersome for birth dates, so thank you for pointign it out. It is up to the implementor (i.e. the OP) to decide what is the correct border to include.

    And to clarify, the query itself is this part
    Code:
    DECLARE @today datetime
        SET @today = DateAdd(dd, DateDiff(dd, 0, GetDate()), 0)
    
    SELECT c.*
    FROM   @customers c
     CROSS
      JOIN (
            SELECT n
            FROM   @numbers
            WHERE  n < 250
           ) n
    WHERE  DateAdd(yy, n.n, c.birth_date) BETWEEN @today AND DateAdd(dd, 30, @today)
    The use of the @today variable is optional (resons explained in post #3) and the number in bold is discussed above.
    George
    Home | Blog

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I suppose one could work out the age of the oldest and youngest people in the database and use those values to filter the upper and lower bounds of the values from @numbers. Efficient if dob is indexed and dob is sense checked on entry\ constrained.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Depending on the number of customers involved, I agree, that may be a good idea. Easy enough to set up as well!
    George
    Home | Blog

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by pootle flump
    I would make some changes, especially your accounting for people up to the age of 250. I like to code defensively too but no one will hit that age for a minimum of another 130 years!
    You've clearly never met some of my grade school teachers. They described the first Thanksgiving celebration in first person terms.

    -PatP

Posting Permissions

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