CODEWITHSUNDEEP
mysql
Alec
Alec

Reputation: 177

Next closest date and time in MySQL

Inside of my Auctions table, I have a column called Auction_StartDate. The value of a row is like this: 2012-10-27 13:45:30.

I need a query that will return the next closest date and time after that. So if the next Auction_StartDate is 2012-10-27 18:30:00, it should return that before the date turns to 2012-10-28.

Upvotes: 0

Views: 1176

Answers (4)

Vikram Jain
Vikram Jain

Reputation: 5588

   SELECT (case when Hour(StartDate)>=12 then DATE_ADD(StartDate,
                  INTERVAL 1 DAY) else StartDate end) as 'date' FROM table  
    ------------------------------
    pleaes add your column name where is static date : 
    est on : http://sqlfiddle.com/#!2/b8435/19

    SELECT (case when Hour(StartDate )>=12 then 
       DATE_FORMAT( DATE_ADD(StartDate ,INTERVAL 1 DAY), '%Y-%m-%d')                
       else DATE_FORMAT(StartDate , '%Y-%m-%d') end) as 'date'  from tabel

Upvotes: 0

Ravi Kumar
Ravi Kumar

Reputation: 226

May be this one helps

    SELECT DATE(Auction_StartDate) closestDate
    FROM    Auctions
    WHERE   DATE(Auction_StartDate) > '2012-10-27'
    order by Auction_StartDate ASC
    limit 1

Upvotes: 0

John Woo
John Woo

Reputation: 263693

You can use MIN to find the closest value without using LIMIT and ORDER BY clause.

SELECT  MIN(DATE(Auction_StartDate)) closestDate
FROM    Auctions
WHERE   DATE(Auction_StartDate) > '2012-10-27'

SQLfiddle Demo

Upvotes: 1

lc.
lc.

Reputation: 116448

If you mean to do this for every row, try this:

SELECT a1.id,
    (SELECT MIN(a2.Auction_StartDate) 
     FROM Auctions a2 
     WHERE a2.Auction_StartDate > a1.Auction_StartDate) AS nextStartDate
FROM Auctions a1

Upvotes: 1

Related Questions