TSQL ORDER BY with nulls first or last (at bottom or top)
In standard SQL you can specify where to put nulls:
order by col asc nulls firstorder by col asc nulls lastorder by col desc nulls firstorder by col desc nulls last
but T-SQL doesn't comply with the standard here. The order of NULLs depends on whether you sort ascending or descending in T-SQL:
order by col asc -- implies nulls firstorder by col desc -- implies nulls last
With integers you could simply sort by the negatives:
order by -col asc -- sorts by +col desc, implies nulls firstorder by -col desc -- sorts by +col asc, implies nulls last
But this is not possible with dates (or strings for that matter), so you must first sort by is null / is not null and only then by your column:
order by case when col is null then 1 else 2 end, col asc|desc -- i.e. nulls firstorder by case when col is null then 2 else 1 end, col asc|desc -- i.e. nulls last
Select * From YourTable Order By case when DateCol is null then 0 else 1 end ,DateCol
Or even Order By IsNull(DateCol,'2525-12-31')
order by case when col_name is null then 1 else 2 end, col_name asc
did the trick on Oracle. However the same on MS SQL Server pushes the NULL records down leaving non null to be on top of the result set.