CODEWITHSUNDEEP
mysqlsql
LukeDS
LukeDS

Reputation: 141

Compare now() and return the closest previous date and the closest next date SQL

How can I execute an sql query that returns two values only, comparing a date type before now() and one after now().

I have created an sql query for one solution, but how can I combine the below sql query to return previous and after now()

SELECT *
FROM Orders
WHERE ordersDate > now()
AND ordersDate IS NOT NULL
ORDER BY ordersDate ASC
LIMIT 1;

Another sql query that I am trying:

 SELECT *
 FROM Orders  
 WHERE
 (SELECT min(ordersDate) FROM Orders 
 Where ordersDate > now()) 
 AND
 (SELECT max(ordersDate) FROM Orders 
 Where ordersDate < now());

This sql returns more than two values which is not what I want, I just one the before and after now(), two value only.

Expected output given now() value of todays: (I dont know how to create a table here)

OrdersID    ordersDate               OrderMade
    4             19/09/2018            yes
    7             21/09/2018            PreOrder

Upvotes: 1

Views: 157

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

One way is to have two separate SELECT queries. One query fetching the order before the current datetime, and another query fetching after the current datetime. We can use Order By Limit clause, to fetch only one row per Select statement. Then, you can use UNION ALL, to get a combined result.

Try the following query:

(
 SELECT *
 FROM Orders
 WHERE ordersDate > NOW() 
 ORDER BY ordersDate ASC LIMIT 1
) 
UNION ALL 
(
 SELECT *
 FROM Orders
 WHERE ordersDate < NOW() 
 ORDER BY ordersDate DESC LIMIT 1
)

Upvotes: 2

Related Questions