We help IT Professionals succeed at work.

SQL Server - Order by date Asc but make null values appear at end instead of the beginning

I have a query that is returning data containing a column that has datetime data.  Some of the fields in this column are potentially null.  I want to order the data by the date in an asc direction.  When I do this, the records that have a null in this column are at the top of the data.  I would like to have them at the bottom of the data set.  How can this be done?
Watch Question

Database Administrator
Basically, I would assign a bogus date to the NULL values that is quite far in the future.  Something like:

CASE When myDate Is Null Then '1/1/2500' Else myDate End

If you post the query you're using I can show you what I mean.


Got it.  I created another column in the select list that did a test to see if the date was Null or not and return a 1 or a 0.  Then a ordered by that contrived column before I order by the date.
	   ,l.Location as SubLocation
	   ,ISNULL(lt.LocationType, '') + ISNULL('.' + lp.Area, '')  + ISNULL('.' + l.Location, '') AS Location	
	   ,(CASE WHEN o.ClearanceStartDateTime IS NULL THEN 0 ELSE 1 END) AS HasDate 
		tbOutage o INNER JOIN tbLocation l ON o.LocationID = l.LocationID
			INNER JOIN tbArea lp ON l.AreaID = lp.AreaID
			INNER JOIN tbLocationType lt ON l.LocationTypeID = lt.LocationTypeID

	ORDER BY HasDate DESC, o.ClearanceStartDateTime

Open in new window


Thanks for posting.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.