...

View Full Version : Bloated birthday query-- is there a better way?



Fumigator
09-13-2006, 06:49 PM
I set out writing a query that determines birthdays coming up in the next 2 months. I have stored the original birth date, so I had to plug in the current year for the comparison, but then in december I wouldn't get january's birthdays, so I added an if statement to plug in current year or next year depending on if the birthday has already passed for the year. Anyway, I ended up with this:



$query = sprintf("
SELECT people_nm,
date_format(birth_dt, '%%b %%D') as birth_dt_f,

date_format(current_date, '%%Y') - date_format(birth_dt, '%%Y') as age_nr,

if (concat(year(current_timestamp), '-', date_format(birth_dt, '%%m'), '-', date_format(birth_dt, '%%d')) >= current_date,
concat(year(current_timestamp), '-', date_format(birth_dt, '%%m'), '-', date_format(birth_dt, '%%d')),
concat(year(current_timestamp + interval 1 year), '-', date_format(birth_dt, '%%m'), '-', date_format(birth_dt, '%%d'))) as birthday

FROM people_tbl

WHERE if (concat(year(current_timestamp), '-', date_format(birth_dt, '%%m'), '-', date_format(birth_dt, '%%d')) >= current_date,
concat(year(current_timestamp), '-', date_format(birth_dt, '%%m'), '-', date_format(birth_dt, '%%d')),
concat(year(current_timestamp + interval 1 year), '-', date_format(birth_dt, '%%m'), '-', date_format(birth_dt, '%%d')))
between current_date and (current_date + interval 2 month)

ORDER BY birthday
");


Which works, but it's so bloated I wonder if there's a better way?

Beagle
09-13-2006, 08:18 PM
Try:

SELECT people_nm WHERE birth_dt between CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 2 MONTH);

Fumigator
09-13-2006, 08:34 PM
I wish I could do that, but the birthdays stored are the actual days of birth, i.e. 1969-02-07, so I have to convert the year to current year (or current year + 1 if the birthday has already happened in current year).

NancyJ
09-13-2006, 08:56 PM
wow thats a gnarly query

Hmmm, this is completely off the top of my head so it might not work but it cant hurt to try heh



$now = date("m-d");
$then = date("m-d", strtotime("+ 2 months"));

$sql = "SELECT people_nm WHERE date_format(birth_dt, '%m-%d') between '$now' and '$then'";


or in fact could you do



SELECT people_nm WHERE date_format(birth_dt, '%m-%d) BETWEEN date_format(CURDATE(), '%m-%d') AND date_format(DATE_ADD(CURDATE(), INTERVAL 2 MONTH), '%m-%d');

...probably wont work but its worth a shot?

NancyJ
09-13-2006, 08:58 PM
ahhh just thought - that doesnt account for the year crossover.

Herm, if your query works then theres no need to change it right? ;)

Beagle
09-13-2006, 09:06 PM
how about:

DATEDIFF(birth_dt, CURDATE())%365

Sure, that's off by one day for each leap year, but that means only people older than 7 will notice!!!

Ok, so no dice there.

You could try: MONTH(birth_dt) BETWEEN MONTH(CURDATE()) AND MONTH(CURDATE())+2

guelphdad
09-15-2006, 03:47 PM
This will get birthdays next month and the month after:



select
columna,
columnb,
columnc
from yourtable
where
month(birthdaycolumn) IN
(
month(date_add(curdate, interval 1 month)),
month(date_add(curdate, interval 2 month)),
)



so those would be October and November birthdays even if you were calculating on September 1st or September 30th.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum