php - SQL get nearest date record
Get the solution ↓↓↓This is an sample data:
Booking_id Name start_date
1 abc 1/1/2018
2 efg 5/2/2018
3 pqr 16/1/2018
4 xyz 19/2/2018
I want this is to be in order nearest to today date on top and past date in last
Answer
Solution:
You need{-code-2}
function on column{-code-3}
. Below is the query which will produce your desired result.
select * from table1
order by Start_Date {-code-2};
You can check sqlfiddle demo here
If the dates are in future, you have to use{-code-5}
to get your desired result.
select * from table1
order by Start_Date {-code-5};
If your dates are mix of Past and future dates like below sample data.
{-code-7}
Below query can be a option to show data in more friendly format.
select * from (
select * from table1
where start_date < current_date
order by start_date {-code-2}
) as B
union
select 0,'{-code-10}_DATE', current_date
union
select * from (
select * from table1
where start_date > current_date
order by start_date {-code-5}
) as A
It will sort past dates data in{-code-2}
order, then add{-code-10}
date to result and then add future data in{-code-5}
format as below.
ID Name Start_Date
Answer
check SQLfiddle demo here
Answer
Answer
Answer
Solution:
You can use the following query:
SELECT Booking_id, Name, start_date
FROM mytable
ORDER BY ABS(DATEDIFF(start_date, NOW()));
TheORDER BY
clause sorts by the distance in days from today's date. The date having the smallest distance comes first.
Answer
Solution:
UseORDER BY
function of sql. Like this:
SELECT *
FROM
table_name
ORDER BY
start_date DESC;
Answer
Solution:
As per my understanding below would be your query, let me know further.
Use can use Order by with ASC|Desc based on requirement,
select * from booking_table order by start_date DESC;
Answer
Solution:
You want nearest date from todate so you can try followuing query
SELECT * FROM table
WHERE start_date >= now()
ORDER BY start_date ASC;
OR
If you want it in revere order then:
SELECT * FROM table
WHERE start_date <= now()
ORDER BY start_date DESC;
Answer
Solution:
this works for you,
select * from table_name Order By start_date Desc;
Answer
Solution:
Based on one of your comments:
today's records follow by future records and then old records at the end
this will sort today and future dates first, followed by past dates:
ORDER BY
CASE WHEN start_date >= CURRENT_DATE THEN 1 ELSE 2 END,
start_date
Results in both new and old dates sorted ascending, if you want the old dates sorted descending:
ORDER BY
CASE WHEN start_date >= CURRENT_DATE THEN 1 ELSE 2 END,
ABS(CURRENT_DATE - start_date)
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: port 80 in use by "unable to open process" with pid 4!
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.