5

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 ?

Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
sxio
  • 53
  • 1
  • 3
  • Would it be accurate to rephrase that as "select all dates greater than the current date?" – erik258 Jan 02 '17 at 18:03
  • As shown on this answer, http://stackoverflow.com/questions/6186962/sql-query-to-show-nearest-date, ORDER BY ABS( DATEDIFF(Field, NOW() ) ) – Youn Elan Jan 02 '17 at 18:04
  • @DanFarrell yeah, but i want it less than 2017-01-05 for Q1 (i dont want date no4 to show) – sxio Jan 02 '17 at 18:05
  • on what basis do you want the dates to show? Within the current day? Or the next 24 hours? – Rob Sedgwick Jan 02 '17 at 18:08
  • @YounElan i saw that solution earlier, but it seems that my condition didnt fit for using LIMIT – sxio Jan 02 '17 at 18:08
  • Ok, @sxio, I'm even more confused now. You need to fix your question to ask exactly what you want. – erik258 Jan 02 '17 at 18:08
  • Do you need to select the closest ones that are on the same day? – Gurwinder Singh Jan 02 '17 at 18:09
  • @RobSedgwick the closest date from today, example: today is 2017-01-03, i want to select date 2017-01-06 10:00:00, 2017-01-06 11:00:00, and 2017-01-06 13:00:00, but i didnt want date more than 2017-01-06, any solution ? – sxio Jan 02 '17 at 18:10
  • @GurV yess, closest one from today, on the same day... – sxio Jan 02 '17 at 18:12
  • @DanFarrell im sorry, im really bad at english, its diifcult to ask what i really want, but i guess, what i want is to select the same closest day from today... – sxio Jan 02 '17 at 18:15

4 Answers4

3

Try this:

select `date`
from your_table
where date(`date`) = (select min(date(`date`))
    from your_table
    where date(`date`) > date(now())
);
Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
1

Try this:

SELECT *
FROM Table 
WHERE datecol > NOW() 
AND datecol < CURDATE() + INTERVAL 1 DAY
LIMIT 1
Aᴍɪʀ
  • 7,623
  • 3
  • 38
  • 52
Rob Sedgwick
  • 4,342
  • 6
  • 50
  • 87
1

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

Faisal
  • 4,591
  • 3
  • 40
  • 49
0

Try this, 

select top 1 * from tblDate where date >GETDATE()  order by date asc
Jackson V Jose
  • 29
  • 4