List:General Discussion« Previous MessageNext Message »
From:Chris Date:August 4 2006 7:28am
Subject:Re: Calculating birthdays and distances... Is there a bug?
View as plain text  
Daevid Vincent wrote:
> I posted this as a comment on the page too, but I’m 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?

Was 1972 a leap year? In which case the day of year will be higher for 
that year than it is for this year because of the extra day in Feb.
Thread
Calculating birthdays and distances... Is there a bug?Daevid Vincent4 Aug
Re: Calculating birthdays and distances... Is there a bug?Chris4 Aug