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'
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]))
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)
Date NDays 2016-10-06 -4 2016-10-14 4 2016-10-31 21 2016-03-10 -214