SQLServerCentral Article

When's Your Anniversary

,

When Is Your Anniversary?

Introduction

Do you remember when your wedding anniversary is? The birthdays of your in-laws? Other important

dates? A reader posted a question about how you can query for important dates that occur within

a time span. At first, this would seem to be a simple task, but as I conducted a few experiments, it

turned out to not be as straightforward as I thought.

The Problem

All important

dates have some beginning date, a "birth date" of sorts. Of course, birthdays have a

birth date as well. This is the first occurrence of the event. It also occurs every year on that

some month and day as the first occurrence. So how do you query for all the events that are coming up

in the next, say, one month? It's not as straightforward as one would expect.

Suppose I have a table that looks like the following:

Person        Birthday
-----------   -------------
Steve         09/15/1967
Tia           02/01/1969
Kendall       05/15/2001
Delaney       11/18/1998
Kyle          06/01/1992

and I want to find out whose birthday will occur in this month or next month and send a reminder.

I can easily find today's date and the end date for next month, but how do I find which dates occur

within this timeframe? If I query for anything between the two dates, nothing will be returned

because none of the events occur within this timeframe. Suppose today is May 3, then I

am looking for events between May 3 and June 30. I can run the following:

create table myTest
(person varchar( 80)
, birthday datetime
)
go
insert MyTest select 'Steve', '09/15/1967'
insert MyTest select 'Tia', '02/01/1969'
insert MyTest select 'Kendall', '05/15/2001'
insert MyTest select 'Delaney', '11/18/1998'
insert MyTest select 'Kyle', '06/01/1992'
go
declare @today datetime
, @lastday datetime
select @today = '05/03/2001'
select @lastday =
  
  
  

    
    
    
    
    

  
'06/30/2001' select *
from MyTest  where
birthday > = 
 
  
  @today  and
 birthday < = @lastday

This returns the following:

Person        Birthday
-----------   -------------
Kendall       05/15/2001

which is not the correct result set. What we really should be returning is both Kendall and Kyle since

they both have birthdays in this range. OK, so we need to change the query to not use the date compares and

instead examine the month and date. Let's try:

declare @today datetime
, @lastday datetime
select @today = '05/03/2001'
select @lastday = '06/30/2001'
select *
 from MyTest
 where month( birthday) >=
  
  
month(@today) and  month(
birthday) < = 
 
  
  month( @lastday)  and
 day( birthday) > =  
   day(@today)  and
 day( birthday) < = day( @lastday)

This returns the following:

Person        Birthday
-----------   -------------
Kendall       05/15/2001

We are still not getting what we want. Why not? Because we are comparing

months and dates together, rather than month and then date. Now I can implement

some subqueries to find items within the month range and then find those within

the date range, but I have a better solution (I think).

The Solution

The first thing that I want to do is make my script a bit more general so I can test a range of

dates. My first change it to calculate the end date of the next month. The first few line of the

script change to:

declare @today datetime
, @lastday datetime
, @months int

select @months = 2
select @today = '05/03/2001'
select @lastday = dateadd( day, -1 * (day(@today)), dateadd( month, @months, @today))

This uses a great technique submitted by jasoningram in our forum.

Now, instead of looking at days and months, I decided that the thing I really wanted to do was

use the date functions to compare the date on which an event occurs with the date range. To do

that I needed to "move" the dates from the table to the current year. I did not, however, want to

change the table data. Instead, I took a cue from Mr. Ingram and wrote this query:

declare @today datetime
, @lastday datetime
, @months int
select @months = 2
select @today = '05/03/2001'
select @lastday = dateadd( day, -1 * (day(@today)), 
dateadd( month, @months,
@today)) select *
from MyTest where
dateadd( year,  datediff(
year, birthday,  @today),
birthday) > =         
 
  
        @today  and
 dateadd( year, datediff( year, birthday, @today), birthday) < = @lastday

This now returns both expected dates.

Person        Birthday
-----------   -------------
Kendall       05/15/2001
Kyle          06/01/1992

By using the built in date functions to calculate the years between the current date and each date

allows me to "move" each date to the present year using the dateadd function. However, we are not done

yet. The advanced SQL guys out there probably caught the one bug right away.

What about year ends?

Suppose today is 11/01/2001 and I am looking for all events in the next 4 months. This would mean

that I want to return both Delaney (11/18) and Tia (2/1). If I change my date and timeframe, I get:

declare @today datetime
, @lastday datetime
, @months int
select @months = 4
select @today = '11/01/2001'
select @lastday = dateadd( day, -1 * (day(@today)), 
dateadd( month, @months,
@today)) select *
from MyTest where
dateadd( year,  datediff(
year, birthday,  @today),
birthday) > =         
 
  
        @today  and
 dateadd( year, datediff( year, birthday, @today), birthday) < = @lastday

This only returns one date, however.

Person        Birthday
-----------   -------------
Delaney      11/18/1998

Why?

Let's examine what happens when we "move" all the dates. If we print out the dates after the calculation

we can see ths following:

declare @today datetime
, @lastday datetime
, @months int
select @months = 4
select @today = '11/01/2001'
select @lastday = dateadd( day, -1 * (day(@today)), dateadd( month, @months, @today))
select birthday
, dateadd( year, datediff( year, birthday, @today), birthday)
 from MyTest

this returns:

1967-09-15 00:00:00.000 2001-09-15 00:00:00.000
1969-02-01 00:00:00.000 2001-02-01 00:00:00.000
2001-05-15 00:00:00.000 2001-05-15 00:00:00.000
1998-11-18 00:00:00.000 2001-11-18 00:00:00.000
1992-06-01 00:00:00.000 2001-06-01 00:00:00.000

I left out the names, but we can see that all the dates were moved to the

year 2001. However, the @lastday value is "02/28/2002". This is one year into

the future. So how can we fix this?

Well, I haven't found a way to do this in a single query, but I did find a way to do this in

two queries, using the UNION operator to combine them. What I reasoned was that for any date whose

month is the same or greater than the current month (from @today) needs to be moved to the current

year. Any date from a month prior to the current month, however, needs to be moved to the next

year. This allows the standard date functions to perform the comparisons correctly. My new

query is:

declare @today datetime
, @future datetime
, @months int
select @months = 4
select @today = '11/1/01'
select @future = dateadd( day, -1 * (day(@today)), dateadd( month, @months, @today))
select *
 from MyTest
 where dateadd( year, datediff( year, birthday, @today), birthday) >=
 @today and
dateadd( year, datediff(
year, birthday, @today),
birthday) < = 
@future and  month(
birthday) > =         
 
  
    month( @today) union select *  from
 MyTest where dateadd( year, datediff( year, birthday, @today)  +
 1, birthday) > =  
 
 @today and
 dateadd( year, datediff( year, birthday, @today) + 1, birthday) < = 
          @future  and
 month( birthday) <  month( @today )

this returns:

Person        Birthday
-----------   -------------
Delaney      11/18/1998
Tia           02/01/1969

Conclusions

While not too terribly complex, this was more difficult than I had thought at first glance.

It was also an interesting problem that I had never encountered, but one that would be useful

in any environment that is handling repeating events. Hopefully you learned something or found

a solution to a problem that you have had.

I am sure some of you will have some great ideas for enhancing this solution. As always

I welcome feedback on this article using the "Your Opinion" button below. Please also

rate this article.

Steve Jones

©dkRanch.net October 2001


Return to Steve Jones Home

Rate

2 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

2 (1)

You rated this post out of 5. Change rating