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