Question: mysql select closest date from today

Question

mysql select closest date from today

Answers 4
Added at 2017-01-02 19:01
Tags
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 ?

Answers
nr: #1 dodano: 2017-01-02 19:01

Try this:

select `date`
from your_table
where date(`date`) = (select min(date(`date`))
    from your_table
    where date(`date`) > date(now())
);
nr: #2 dodano: 2017-01-02 19:01

Try this:

SELECT *
FROM Table 
WHERE datecol > NOW() 
AND datecol < CURDATE() + INTERVAL 1 DAY
LIMIT 1
nr: #3 dodano: 2017-01-03 08:01

Try this,

select top 1 * from tblDate where date >GETDATE()  order by date asc
nr: #4 dodano: 2017-01-04 20:01

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

Source Show
◀ Wstecz