TSQL ORDER BY with zeros first or last (bottom or top)

Question

I have a date column that has NULL . I want to order by an ASC date column, but I need NULL be at the bottom. How to do it in 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 is not up to standard here. The NULL order depends on whether you sort the up or down in T-SQL:

 order by col asc -- implies nulls first order by col desc -- implies nulls last 

With integers, you can just sort by 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 null or not null, but only by column:

 order by case when col is null then 1 else 2 end, col asc|desc -- ie nulls first order by case when col is null then 2 else 1 end, col asc|desc -- ie 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')

Answer 3

order by case when col_name is null then 1 else 2 end, col_name asc succeeded in Oracle. However, the same thing on MS SQL Server shifts NULL records down, leaving a nonzero value at the top of the result set.