SQL get nearest date record

Question

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

Possible duplicate of [Order by descending date - month, day and year](https://stackoverflow.com/questions/4676139/order-by-descending-date-month-day-and-year) — BlackBeard, Aug 18 '17 at 05:28

zarruq · Accepted Answer · 2017-08-18T06:00:28.980

You need SORT desc function on column start_Date. Below is the query which will produce your desired result.

select * from table1
order by Start_Date desc;

You can check sqlfiddle demo here

If the dates are in future, you have to use asc to get your desired result.

select * from table1
order by Start_Date asc;

If your dates are mix of Past and future dates like below sample data.

ID Name   Start_Date
---------------------
1  abc   2018-01-01
2  efg   2018-02-05
3  pqr   2018-01-16
4  xyz   2018-02-19
1  abc   2017-01-01
2  efg   2017-02-05
3  pqr   2017-01-16
4  xyz   2017-02-19

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 desc
) as B
union
select 0,'TODAY_DATE', current_date
union
select * from (
select * from table1
where start_date > current_date
order by start_date asc
) as A

It will sort past dates data in desc order, then add TODAY date to result and then add future data in asc format as below.

 ID  Name        Start_Date
--------------------------
4   xyz         2017-02-19
2   efg         2017-02-05
3   pqr         2017-01-16
1   abc         2017-01-01
0   TODAY_DATE  2017-08-18
1   abc         2018-01-01
3   pqr         2018-01-16
2   efg         2018-02-05
4   xyz         2018-02-19

check SQLfiddle demo here

this return record in reverse order not the nearest date to today — Paritosh Mahale, Aug 18 '17 at 05:34

score 3 · Answer 2 · answered Aug 18 '17 at 05:39

You can use the following query:

SELECT Booking_id, Name, start_date
FROM mytable
ORDER BY ABS(DATEDIFF(start_date, NOW()));

The ORDER BY clause sorts by the distance in days from today's date. The date having the smallest distance comes first.

Demo here

BlackBeard · Answer 3 · 2017-10-09T05:43:58.050

2

Use ORDER BY function of sql. Like this:

SELECT * 
FROM 
     table_name 
ORDER BY 
   start_date DESC;

edited Oct 09 '17 at 05:43

answered Aug 18 '17 at 05:27

BlackBeard

10,246
7
52
62

score 1 · Answer 4 · answered Aug 18 '17 at 05:28

1

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;

answered Aug 18 '17 at 05:28

Sam

1,106
10
14

1

this return record in reverse order by date not the nearest record to today's date – Paritosh Mahale Aug 18 '17 at 05:36
Dear Paritosh, Reverse order from today means the same it will render the nearest records in descending order to today's date. – Sam Oct 16 '17 at 06:37

B. Desai · Answer 5 · 2017-08-18T05:43:17.697

1

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;

edited Aug 18 '17 at 05:43

answered Aug 18 '17 at 05:33

B. Desai

16,414
5
26
47

thank you for response it solve my half problem now i am getting records from today's date but i want my old records to be append at the end how can i do that? – Paritosh Mahale Aug 18 '17 at 05:47
yes i have tried it without limit it returns records of today and from future but not the records having start_date < todays date i want this records to be at the end – Paritosh Mahale Aug 18 '17 at 05:49
You want all records? Today, future and past? – B. Desai Aug 18 '17 at 05:51
yes order will be like this- today's records follow by future records and then old records at the end – Paritosh Mahale Aug 18 '17 at 05:52

score 1 · Answer 6 · answered Aug 18 '17 at 06:19

1

this works for you,

select * from table_name Order By start_date Desc;

answered Aug 18 '17 at 06:19

Sanjay Kumaar

690
7
17

score 1 · Answer 7 · answered Aug 18 '17 at 10:28

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)

SQL get nearest date record

7 Answers7