Results 1 to 3 of 3

Thread: date retrieval

  1. #1
    Join Date
    May 2013
    Posts
    14

    Unanswered: date retrieval

    I am using SQL SERVER REPORTING SERVICES 2008 R2. I am needing to retrieve a set of records from a database for any members who will be 70 1/2 years of age at any time during the current year. I need to get all members who have birth dates on or before 6-30 70 years prior to the current year. Ex. for a member whose birthday is 7-5-1943, I don't want to see that record returned. However, for a member whose birthday is 4-15-1943, I do want that record included in the returned data.

    Any help would be greatly appreciated.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    When does the "current year" begin?

    Assuming that you mean the calendar year (January 1 through December 31), then the following code ought to work nicely:
    Code:
    --  Create a table of sample dates
    
    CREATE TABLE #myDates (
       d            DATE
       CONSTRAINT XPK_myDates
          PRIMARY KEY (d)
       )
    
    --  Populate it with a few mid-month values
    
    INSERT INTO #myDates (d)
       SELECT DateAdd(m, n.number, '1850-01-15')
          FROM master.dbo.spt_values AS n
    	  WHERE  'P' = n.type
    
    --  Pick our "as of" date for running the query for testing
    
    DECLARE @asOf  DATETIME = GetDate()
    
    --  Actually run the query
    
    SELECT d
       FROM #myDates
       WHERE  DateAdd(m, 846, d) < DateAdd(year, DateDiff(year, 0, @asOf), 365)
       ORDER BY d DESC
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    The only date format allowed in ANSI/IS Standard SQL is ISO-8601, which is 'yyyy-mm-dd'; your format is ambiguous and not standard. Here is a guess at the DDL you did not post.

    retrieve a set of records {sic: rows are not records} from a database for any members who will be 70 years of age at any time during the current year.
    CREATE TABLE Membership
    (member_id CHAR(16) NOT NULL PRIMARY KEY,
    birth_date DATE NOT NULL,
    ..);

    So we want to know the date they hit 70, which is 845 months after their birthdate.

    SELECT member_id, birth_date, DATEADD (MONTH, 846, birth_date) AS seventy_half_date
    FROM Membership
    WHERE DATEADD (MONTH, 846, birth_date)
    BETWEEN '2013-01-01' AND '2013-12-31';

Posting Permissions

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