php - SQL get nearest date record

872

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



    
     
       Undefined answered 
     
     
       2022-09-7
     
    
    
     
       Link to answer


    
    140
  votes


        	       Answer        	       
		------
4   xyz         2017-02-19
2   efg         2017-02-05
3   pqr         2017-01-16
1   abc         2017-01-01
0   {-code-10}_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

258

Answer

- 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|||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|||desc|||TODAY|||asc|||ID Name Start_Date

760

Answer

------ 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

903

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.

736

Answer

Solution:

UseORDER BY function of sql. Like this:

SELECT * 
FROM 
     table_name 
ORDER BY 
   start_date DESC;

211

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;

616

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;

If you want it in revere order then:

SELECT * FROM table 
WHERE start_date <= now()
ORDER BY start_date DESC;

353

Answer

Solution:

this works for you,

select * from table_name Order By start_date Desc;

494

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)

People are also looking for solutions to the problem: php - How to get all tax rates from Magento2 through rest api?

Source

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.

Ask a Question