Results 1 to 5 of 5

Thread: date comparison

  1. #1
    Join Date
    May 2013
    Posts
    14

    Unanswered: date comparison

    I need to write a SELECT query that will return the following:


    MEMBER_ACCT.acn
    MEMBER_ACCT.mbr_nam
    MEMBER_ACCT.birth_dt

    WHERE THE MEMBER_ACCT.birth date is on or before 6-30-yyyy (70 years ago on the date the query is run)

    i.e. if run today (5-31-2013) the birth date would need to be on or before 6-30-1943.

    I'm trying to uses SQL SERVER 2008 R2 Reporting Services to automate this report.....


    Thanks for your assistance in advance...

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    SELECT Column1
    FROM YourTable
    WHERE YourDateField > DATEADD(yyyy,-70,GETDATE())
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by manyquestionstx View Post
    i.e. if run today (5-31-2013) the birth date would need to be on or before 6-30-1943.
    Uh....those anniversary dates are actually a month apart. Is that what you wanted?
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If the June 30th a constant and used for every date in a year, then I'd use:
    Code:
    SELECT d
    ,  DateAdd(year, DateDiff(year, '1900-01-01', d) - 70, '1900-06-30') AS TargetDate
       FROM (SELECT DateAdd(day, number, '2010-01-01') AS d
          FROM master.dbo.spt_values AS z1
    	  WHERE  'P' = z1.type) AS z
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Quote Originally Posted by manyquestionstx View Post
    I need to write a SELECT query that will return the following:


    MEMBER_ACCT.acn
    MEMBER_ACCT.mbr_nam
    MEMBER_ACCT.birth_dt

    WHERE THE MEMBER_ACCT.birth date is on or before 6-30-yyyy (70 years ago on the date the query is run)

    i.e. if run today (5-31-2013) the birth date would need to be on or before 6-30-1943.
    Try:
    Code:
    Select MEMBER_ACCT.acn, MEMBER_ACCT.mbr_nam, MEMBER_ACCT.birth_dt
    From MEMBER_TABLE
    WHERE MEMBER_ACCT.birth_dt < DateAdd(dd, 31, DateAdd(yy, -70, GetDate()))
    Since GetDate also returns the time, it is necessary to go out 31 days and use just < for the compare. This allows boundary cases to be evaluated as TRUE without having to worry about the time.

Posting Permissions

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