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
Is this what you want?
order by abs(datediff(day, getdate(), [date]))
ABS converts all the values to positive values. Here is a official documentation link msdn.microsoft.com/en-us/library/ms189800.aspx
There are two things in the below SQL.
1) First sort by date in descending order
2) With in each month sort the dates in ascending order of day.
( The below DATEPART function will calculate the DAY from DATE. )
SELECT *
FROM DATA
ORDER BY DATEPART( MONTH, date ) DESC, DATEPART( DAY, date );
order by? and btw, sql server doesn't have extract function.
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