While working on an events calendar for ASPAlliance.com, I needed to take birth dates stored in my columnist database and figure out which columnists had birthdays that fell within a particular date range.  I also wanted to retrieve the columnist's age, although I'm not sure if I'll end up displaying that in the final version of the events calendar.  The query also works for anniversary dates.

Basically, the query I came up with has 8 parts that it checks for:

1. Is the dob (date of birth) null?  This check may or may not be necessary depending on your database settings for null comparisons, but including it explicitly makes certain that no results with null birth dates are returned.
2. Is the begin_date parameter less than or equal to the end_date?  If not, we don't need to return any results.
3. Does the birthday fall between the months of the begin and end dates, in the same year?
4. Does the month fall after the begin date OR before the end date, where the begin and end dates fall in different years?
5. Does the birth date fall in the latter part of the begin_date month, where the end_date is in a different month?
6. Does the birth date fall in the early part of the end_date month, where the begin_date is in a different month?
7. Is the birth date between the begin_date and end_date in the same month and year?
8. Is an entire year being returned?  I placed this last because this condition should rarely occur in actual queries.

The first two checks, if false, are sufficient to eliminate a row (or all rows for #2) from the result set.  However, any one of the conditions from 3 through 8 is sufficient to merit returning that row.  That is, the filtering criteria for the query is:

1 AND 2 AND (3 OR 4 OR 5 OR 6 OR 7 OR 8)

Let's take a look at the actual query on the next page.