CODEWITHSUNDEEP
sqlsql-server-2008
MDP
MDP

Reputation: 53

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

Upvotes: 2

Views: 348

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 81960

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

Upvotes: 0

Teja
Teja

Reputation: 13534

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269763

Is this what you want?

 order by abs(datediff(day, getdate(), [date]))

Upvotes: 3

Related Questions