I posted this as a comment on the page too, but Im curious as to why the top solution is off by a day or so... Is this a bug or a rounding issue or what? Is there a way to "fix" the top one to work the way I expect/want it to work? I suspect it's because (as Jack Palance said in 'City Slickers') "the day ain't over yet" that I get the rounding error.
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html SET @DOYNOW = DAYOFYEAR(CURDATE()); SELECT (DAYOFYEAR(birthdate) - @DOYNOW) AS birthdays, birthdate, @DOYNOW, CURDATE() FROM users WHERE birthdate IS NOT NULL; then if birthdays == 0, it's that persons birthday, otherwise you know if the birthday is in the future by how many days, or if you missed it and how many beers you owe them... (although the missed/negative days seems to be off) +-----------+------------+---------+------------+ | birthdays | birthdate | @DOYNOW | CURDATE() | +-----------+------------+---------+------------+ | 83 | 1969-10-26 | 216 | 2006-08-04 | | 3 | 1981-08-07 | 216 | 2006-08-04 | | -1 | 1972-08-02 | 216 | 2006-08-04 | | 0 | 1946-08-04 | 216 | 2006-08-04 | | -151 | 1976-03-05 | 216 | 2006-08-04 | +-----------+------------+---------+------------+ Shouldn't that -1 be -2 ? Am I missing something obvious? If I do "SELECT DATEDIFF('2006-08-01', CURDATE());" I get -2 as I expect. So, I guess the real solution is to use this: SET @YEAR = CONCAT(EXTRACT(YEAR FROM CURDATE()),'-'); SELECT DATEDIFF(CONCAT(@YEAR, DATE_FORMAT(birthdate, '%m-%d')), CURDATE()) AS birthdays, birthdate, CURDATE() FROM users WHERE birthdate IS NOT NULL; +-----------+------------+------------+ | birthdays | birthdate | CURDATE() | +-----------+------------+------------+ | 83 | 1969-10-26 | 2006-08-04 | | 3 | 1981-08-07 | 2006-08-04 | | -2 | 1972-08-02 | 2006-08-04 | | 0 | 1946-08-04 | 2006-08-04 | | -152 | 1976-03-05 | 2006-08-04 | +-----------+------------+------------+ By the way, if you're using PHP or some other scripting language, you can get rid of the @YEAR stuff and just do: DATEDIFF(DATE_FORMAT(birthdate, '".date('Y')."-%m-%d'), CURDATE()) AS birthdays ÐÆ5ÏÐ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
