Working out birthdays between 2 dates

[mpgalvin]

I have a database consiting of customer details, and I have to write a report that will return all customers who have a birthday between 2 dates.

The database holds the dob, so what I did was I brought the dob up to the year of the dates and then just did a BETWEEN() clause. All well and good, but it occurs to me that on some occasions the 2 dates may not be in the same year. It could be 31/12/2001 to 7/1/2002 or something. So my piece of code is useless for that eventuality.

Anyone got any good code for working out if somebody has a birthday between 2 dates if the dates are in different years?

 

[jimstarr]

Set the year of the dob to the EARLIER year of the 2 dates. Then the BETWEEN() function will work.

Jim

 

[mpgalvin]

Jim, that's what I have, but take this example:

date1 31/12/2001
date2 8/1/2002 (8th Jan)
dob 4/1/1976

If I set the dob to date1's year, I get 4/1/2001 which isn't between the 2 dates. This is my problem.

 

[jimstarr]

You're right. Try setting dob1 using the EARLIER year, and dob2 using the LATER year. Then do:

IF BETWEEN(dob1, date1,date2) OR BETWEEN(dob2,date1,date2) ...

I think this works.


Jim

 

[SMCmtg]

MP: What I did was converted to Juilian and wrote the code to set filters from there, say today and 7 days hence. (I am in an industry that has a ton of drop dead dates and needed for those day over day warnings of what's coming.)
My birthday cards to clients was just an extenison of that.
Hope this gives you some ideas. . .

 

[mpgalvin]

Julian dates! Of course, I knew there was something I forgot. If I convert dob to this year, convert it to Julian, then convert the date range to Julian, I can use BETWEEN.

Having said that, of course, Jim's solution looks a lot easier