MDP MDP - 1 month ago 7
SQL Question

Sorting date based on condition in SQL Server

I have an expiration date column on my table, I want to fetch records in order that are nearly to expire. I used 'ORDER BY DATE DESC' but it only arranges the date in descending order.

Output using 'ORDER BY DATE'

2016-10-31
2016-10-06
2016-03-10


desired output:

2016-10-06
2016-10-31
2016-03-10

Answer

As disturbingly brilliant as Gordon is, I think there was an oversight.

Declare @YourTable table (Date date)
Insert Into @YourTable values
('2016-10-14'),           -- Added 10/14 +4 Days
('2016-10-31'),
('2016-10-06'),
('2016-03-10')

Select *, NDays=datediff(day, getdate(), [date]) 
From @YourTable
Order By abs(datediff(day, getdate(), [date]))

Returns

Date        NDays
2016-10-14  4          << Record Added (Should be below 10/6)
2016-10-06  -4
2016-10-31  21
2016-03-10  -214

I think the safest (and far less elegant) would be something more like this

Select *, NDays=datediff(day, getdate(), [date]) 
From @YourTable
Order By Year(Date) Desc,Month(Date) Desc,Day(Date)

Returns

Date        NDays
2016-10-06  -4
2016-10-14  4
2016-10-31  21
2016-03-10  -214