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

Blitzkrieg · #1 (**permalink**) 03-22-04, 15:00

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.

Pat Phelan · #2 (**permalink**) 03-22-04, 15:33

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(year, a.years, a.[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(day, DateAdd(year

,     a.years, a.[myDate]), GetDate()) < 7

   ORDER BY 2, 3

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

-PatP

Blitzkrieg · #3 (**permalink**) 03-22-04, 15:55

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.

Pat Phelan · #4 (**permalink**) 03-22-04, 16:04

Try your example data from your first posting.

-PatP

Internet Services

Web Development

Digital Marketing

Consumer Tech