TSQL ORDER BY with nulls first or last (at bottom or top)

Question

I have a date column which has some NULL. I want to order by the date column ASC, but I need the NULL s to be at the bottom. How to do it on TSQL?

Answer 1

In standard SQL you can specify where to put nulls:

order by col asc nulls first order by col asc nulls last order by col desc nulls first order 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 first order 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 first order 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 first order by case when col is null then 2 else 1 end, col asc|desc -- i.e. nulls last 

Answer 2

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