MySQL - Get next closest day between two days

Question

We have two dates in database:

1. 2013-08-01
2. 2013-08-03

Let say that today's date is 2013-08-02 and we want to get next closest date from database. I've found this query, but it's not getting the next day but previous:

SELECT 
  * 
FROM 
  your_table 
ORDER BY 
  ABS(DATEDIFF(NOW(), `date`))
LIMIT 1

When we run it, we get 2013-08-01 and not 2013-08-03 as we want. What would be the solution?

Answer 1

Others have already posted the answer if you always want a future date:

select *
from your_table
where date > now()
order by date 
limit 1

If there is no future date and you still want to return the last date, then you can do this by modifying the order by clause:

select *
from your_table
order by (date > now()) desc,
         (case when date > now() then date end) ,
         date desc
limit 1;

EDIT (in response to comment):

To handle "today" as the closest date, you need to convert now() to a date and use >=:

select *
from your_table
order by (`date` >= date(now())) desc,
         (case when `date` >= date(now()) then `date` end) ,
         `date` desc
limit 1;

The order by clause has three components. The first is a flag for any future date. This puts future dates first. The second two clauses order by date. The first orders the future dates in ascending order. The past dates all have the same value ('NULL'), so these are affected by the third clause which orders them in descending order.