Re: Birthdate Query

I have used a Month-Day integer field like this for birthdays (mdd) and 
indexed it (only 366 unique values) with reasonable success on a table of a 
few tens-of-thousands of records. It was the only workable solution I ever 
came up with.

Having the low-selectivity index was better than not having it. Cannot say I 
ever tested or compared a string field equivalent.

NB If you want to query eg "whose birthday is it this week", then
things get 
a little trickier when you span 2 calendar years.

I ended up creating a stored procedure accepting a from and to date, doing 
the necessary date manipulations, then using a generic where clause like:

  where
   (BirthMonthDay between :FromMonthDay and :ToMonthDay) or
   (BirthMonthDay >= :AfterMonthDay)

If you spanned 2 years, then the AfterMonthDay clause is required to pick up 
the birthdays on the tail end of the earlier year. If you did not span 2 
years, then AfterMonthDay could be set to 9999 and catch nothing.

I found it more efficient to do this in my case - but you may find it easier 
to compile the SQL in the application instead and only have the 
"AfterMonthDay" clause when actually required...


-- 
Brent Rose
Whats On Report
brent@whatson.co.nz