Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    14

    Red face Unanswered: MS tSQL: Retrieving Anniversary Dates within 1 Week from Today?

    Doing math on dates in SQL always hurts my head ;P

    What I would like to do is retrieve a recordset where the anniversary date
    is within (less than or equal to) 7 days from today's date.

    For example, if today is 03/22/2004, then an anniversary date of 03/18/2001
    should be included in the recordset, because the 3rd anniversay is coming-up
    within 7 days of 'today.'

    Any clue? I'm using MS SQL Server 2000.
    Sincerely,
    Todd M. Taylor
    http://www.toddtaylor.com/

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Every database engine seems to use slightly different date arithmatic. For MS-SQL 7.0 and later I'd use:
    PHP Code:
    SELECT a.years
    ,  Convert(CHAR(10), DateAdd(yeara.yearsa.[myDate]), 121)
    ,  
    Convert(CHAR(10), a.[myDate], 121)
       
    FROM (SELECT [myDate], CASE WHEN DateAdd(year
    ,     DateDiff(year, [myDate], GetDate()), [myDate]) > GetDate() 
          
    THEN DateDiff(year, [myDate], GetDate()) - 1
          
    ELSE DateDiff(year, [myDate], GetDate())
       
    END AS years
       FROM dbo
    .tMyDates) AS a
       WHERE  DateDiff
    (dayDateAdd(year
    ,     a.yearsa.[myDate]), GetDate()) < 7
       ORDER BY 2

    You don't strictly have to keep the years and the event separate, but it makes things easier for me to manipulate.

    -PatP

  3. #3
    Join Date
    Oct 2003
    Posts
    14
    Thanks for the solution, Pat.

    I think I got one I like better though:
    Code:
    WHERE
    DATEDIFF(dd,GETDATE(),DATEADD(yy,DATEDIFF(yy, Users.HireDate,GETDATE()),Users.HireDate)) 
    BETWEEN 0 and 7
    This *seems* to work with the data I have so far. I'm still trying to digest how it works to determine how it handles leap years.
    Sincerely,
    Todd M. Taylor
    http://www.toddtaylor.com/

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Try your example data from your first posting.

    -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
  •