February 7, 2009
@ 03:22 PM
Comments [2]
Query for Birthdays

I just finished up a report which generates a list of customers whose birthdays fall within a specified date range. I wrote a simple query which did essentially:


DECLARE @startDate datetime
DECLARE @endDate datetime
SET @startDate = '2009-2-15'
SET @endDate = '2009-3-14'

SELECT c.iid,
        c.FirstName,
        c.LastName,
        c.BirthDate,
        c.Address1,
        c.Address2,
        c.City,
        c.State,
        c.ZipCode
   FROM Customers c
  WHERE c.BirthDate BETWEEN @startDate AND @endDate


Of course, that didn't work. That is, unless our customers happened to actually just been born (as of when I wrote this they wouldn't have been even born yet). Hmm....My first thought was maybe joining this to a date table to get the month and day split apart but that idea falls apart pretty quickly so I dismissed it. I'll come back to this idea in a second.

The other simple way to do this is to convert the date to a day of the year, so January 1st is 1, Jan. 2nd is 2, December 31st is 365. SQL Server includes a nice DATEPART() function to make this easy - you can specify that you want the day of the year with it.

So my query was rewritten as:

SELECT c.iid,
        c.FirstName,
        c.LastName,
        c.BirthDate,
        c.Address1,
        c.Address2,
        c.City,
        c.State,
        c.ZipCode
   FROM Customers c
  WHERE DATEPART(dayofyear, c.BirthDate)
BETWEEN DATEPART(dayofyear, @startDate) AND DATEPART(dayofyear, @endDate)


While this one works it's a bit slow. That's not so surprising since it has to use DATEPART on the rows to generate the day of the year in the WHERE clause. My actual code filters the customers a bit more but it was still a fair number of records. Ultimately, since this a summary/reporting table that gets populated and updated nightly I just added another (integer) column to store the precalculated day of the year number.

Surprisingly, this version isn't that much faster - maybe 15% or so. Apparently DATEPART is pretty quick.

It wasn't until after making these changes that I realized I could have added a day of the year column to my date table, done a join then used this column in my WHERE clause. That actually would have been easier if it had occurred to me sooner. I'm guessing performance is probably equivalent, especially since adding the column directly in the table didn't have a huge impact on the speed of the query.


 
Categories: SQL

« How to Change the CodeRush Refactor Key | Home | Collapsing Comments Update »
Friday, April 17, 2009 12:06:49 AM (Eastern Standard Time, UTC-05:00)
dayofyear won't work due to leap years. While '2009-12-31' does return 365, you get 366 for '2008-12-31'. Hence, a query for 365 returns birthdays 12/30 in some years and 12/31 in others. You never get a complete list of 12/31 birthdays, though.

Instead of the dayofyear you need to store month*31+day as a number or month, day as a char(4) value, or just query on datepart with day and month checks. The latter isn't as easy, though, if you need to check a range of days.
Christof Wollenhaupt
Tuesday, April 21, 2009 5:30:36 PM (Eastern Standard Time, UTC-05:00)
Interesting. I hadn't thought of that. Thanks for the feedback.
Paul Mrozowski
Name
E-mail
(will show your gravatar icon)
Home page

Comment (Some html is allowed: a@href@title, b, i, strike) where the @ means "attribute." For example, you can use <a href="" title=""> or <blockquote cite="Scott">.  

Enter the code shown (prevents robots):
Live Comment Preview