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?
Comment
Watch Question

Database Administrator
CERTIFIED EXPERT
Commented:
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.

Author

Commented:
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.
SELECT 
	   o.OutageID
	   ,o.LocationID
	   ,lt.LocationType
	   ,l.Location as SubLocation
	   ,ISNULL(lt.LocationType, '') + ISNULL('.' + lp.Area, '')  + ISNULL('.' + l.Location, '') AS Location	
	   ,l.AreaID
	   ,lt.LocationTypeID
	   ,l.SecondaryAreaID
	   ,lp.Area
	   ,o.MaineLCC
	   ,o.ISO
	   ,o.ActivityID
	   ,o.ClearanceStartDateTime
	   ,(CASE WHEN o.ClearanceStartDateTime IS NULL THEN 0 ELSE 1 END) AS HasDate 
	   ,o.ClearanceEndDateTime
	   ,o.CompletionDate
	   ,o.ClearanceHolder
	   ,o.ScopeDescription
	   ,o.Comments
	   ,o.Archived
	FROM 
		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

Author

Commented:
Thanks for posting.

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