Reputation: 10117
Im trying to order results ASCENDING from the current date
this is what im using now;
SELECT * FROM friends JOIN bdays
ON bdays.user = friends.friendname
WHERE username = $userid ORDER BY DATE_FORMAT(date, '%m %d')
any ideas?
example ordering by date now, sorts the birthdays starting at january
what i need, is instead of starting the list at january, is starting it from the current date.
So, instead of;
January
February
March
April
May
June
July
August
September
November
December
It will order them like this;
April (current month/day)
May
June
July
August
September
November
December
January
February
March
April (all the way up to yesterday)
Upvotes: 4
Views: 277
Reputation: 8092
You could try:
ORDER BY
DATE_FORMAT(date,'%m %d') < DATE_FORMAT(NOW(),"%m %d"),
DATE_FORMAT(date,'%m %d');
First, order by whether or not the date is less than the current date, then order by month and date in ascending order.
NOTE This looks like the method Col. Shrapnel was referring to.
Upvotes: 2
Reputation: 563011
Here's how I'd do it:
SELECT *, (DATE_FORMAT(date, '%j')-DATE_FORMAT(NOW(), '%j')+365)%365 AS d
FROM foo ORDER BY d;
The %j
date format is the day of the year, i.e. a number 001...366.
I tested this on some sample data and it sorts in the way you describe: it ignores the year, and sorts the next date that falls after the current date first, then ascending, and wrapping around to dates earlier in the year.
+----+------------+------+
| id | date | d |
+----+------------+------+
| 5 | 1999-05-15 | 27 |
| 6 | 1992-06-15 | 59 |
| 7 | 1990-07-15 | 88 |
| 8 | 1988-08-15 | 120 |
| 9 | 1980-11-15 | 212 |
| 1 | 2010-01-15 | 272 |
| 2 | 2009-02-15 | 303 |
| 3 | 2004-03-15 | 332 |
| 4 | 2002-04-15 | 362 |
+----+------------+------+
Upvotes: 4
Reputation: 3757
you might try:
SELECT *, DATE_FORMAT(date, '%m %d') as adate FROM friends JOIN bdays
ON bdays.user = friends.friendname
WHERE username = $userid
ORDER BY adate
Upvotes: 1
Reputation: 158005
something like order by if(date_format(date,'%m%d') < date_format(now(),'%m%d')),1,0), date_format(date,'%m%d')
Upvotes: 1