mysql select closest date from today

Question

I have below table

    date
-----------------------
1. 2017-01-02 20:59:00
2. 2017-01-04 10:00:00
3. 2017-01-04 11:00:00
4. 2017-01-09 17:20:00

Q1: Assuming today is 2017-01-03, how can i select the above date to get the result of 2 and 3 ?

Q2: assume today is 2017-01-05, get the result of 4 ?

Answer 1

Try this:

select `date`
from your_table
where date(`date`) = (select min(date(`date`))
    from your_table
    where date(`date`) > date(now())
);

Answer 2

Try this:

SELECT *
FROM Table 
WHERE datecol > NOW() 
AND datecol < CURDATE() + INTERVAL 1 DAY
LIMIT 1

Answer 3

Q1 Answer:

SELECT
    *
FROM
    closest_date_table
WHERE
    date(`date`) = (
        SELECT
            min(date(`date`))
        FROM
            closest_date_table
        WHERE
            date(`date`) > date('2017-01-03')
    );

Q2 Answer:

SELECT
    *
FROM
    closest_date_table
WHERE
    date(`date`) = (
        SELECT
            min(date(`date`))
        FROM
            closest_date_table
        WHERE
            date(`date`) > date('2017-01-05')
    );

LIVE SQL FIDDLE DEMO