quote:

---

Originally posted by james.burger

I am trying to sort on Semesters, with the closest semester being returned as the first on the list and the previous semesters appear towards the end of the list.

I currently have the following: SELECT TOP 100 PERCENT semester, begindate, enddate, DATEDIFF(dd, GETDATE(), enddate) AS Dates
FROM dbo.tbl_SDFRSperiods
ORDER BY DATEDIFF(dd, GETDATE(), enddate)

My semesters are: Fall 2009 (8/15/2009 - 12/31/2009), Spring 2010 (1/1/2010 - 6/30/2010), Summer 2010 (7/1/2010 - 8/14/2010), and Fall 2010 (8/15/2010 - 12/31/2010).

The above query gives me Fall 2009, Spring 2010, Summer 2010, Fall 2010. But I would like the query to dynamically sort the semesters based upon the current date. So today is 2/1/2010, the sort would produce Spring 2010, Summer 2010, Fall 2010, and Fall 2009.

On 7/1/2010, the sort would produce Summer 2010, Fall 2010, Fall 2009, Spring 2010.

I tried the following:
SELECT TOP 100 PERCENT semester, begindate, enddate, DATEDIFF(dd, GETDATE(), enddate) AS Dates
FROM dbo.tbl_SDFRSperiods
ORDER BY (CASE Dates WHEN Dates > 0 THEN Dates ELSE Dates + 10000 END) ASC

But I am having no luck. Thanks!

---