Question: 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)

```

Answer: 
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 |
+----+------------+------+

```
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

```