CODEWITHSUNDEEP
mysqlsql
sxio
sxio

Reputation: 53

mysql select closest date from today

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 ?

Upvotes: 5

Views: 6110

Answers (4)

Faisal
Faisal

Reputation: 4767

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

Upvotes: 1

Jackson V Jose
Jackson V Jose

Reputation: 29

Try this, 

select top 1 * from tblDate where date >GETDATE()  order by date asc

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

Try this:

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

Upvotes: 3

Rob Sedgwick
Rob Sedgwick

Reputation: 4514

Try this:

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

Upvotes: 1

Related Questions