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.
