I like the idea of using DAYOFYEAR(), but your implementation doesn't handle wrapping around between years. For example if the anniversary date is Jan 2nd then DAYOFYEAR()-14 would return -12.
On the MySQL website I found the following which works quite well: SELECT name, birthday, IF(DAYOFYEAR(birthday) >= DAYOFYEAR(NOW()), DAYOFYEAR(birthday) - DAYOFYEAR(NOW()), DAYOFYEAR(birthday) - DAYOFYEAR(NOW()) + DAYOFYEAR(CONCAT(YEAR(NOW()),'-12-31'))) AS distance FROM birthdates; This works out the distance between now and the birthday, and it handles wrapping around to the next year if necessary. On Jun 23, 4:57 pm, Boyd <[email protected]> wrote: > I would use a date field so you can then use the mysql date functions > easily, just use any year you like when you store them > > then something like > > SELECT * FROM foo WHERE DAYOFYEAR(anniversary_date)-14 = > DAYOFYEAR(curdate()) > > 14 days in future. > > DAYOFYEAR() is useful for this sort of thing, theres also DAY() and > MONTH() > > On Jun 23, 4:09 pm, Super Steve <[email protected]> wrote: > > > > > I have a need to record an anniversary date with just a day and month > > (year isn't important so I don't need to record it). > > > I'm using MySQL 5.0.26 and was wondering what the best way is to store > > the anniversary and then do queries on it. > > > The query I need to do is to select all records that have an > > anniversary date 2 weeks in the future. > > > Should I split the anniversary date into two numeric fields or somehow > > keep it as one? Maybe convert it to some sort of "day of year" number > > (what about leap years?)? > > > I will be using a form to get the anniversary, and also to allow > > editing of the anniversary. I was thinking of having two select > > lists, one for day (0 - 31) and one for month (Jan - Dec). > > > Any ideas on the best way to handle this? -- NZ PHP Users Group: http://groups.google.com/group/nzphpug To post, send email to [email protected] To unsubscribe, send email to [email protected]
