SQL Server ORDER BY date and nulls last

I am trying to order by date. I want the most recent dates coming in first. That's easy enough, but there are many records that are null and those come before any records that have a date.

I have tried a few things with no success:

ORDER BY ISNULL(Next_Contact_Date, 0)

ORDER BY ISNULL(Next_Contact_Date, 999999999)

ORDER BY coalesce(Next_Contact_Date, 99/99/9999)

How can I order by date and have the nulls come in last? The data type is smalldatetime.

smalldatetime has range up to June 6, 2079 so you can use

ORDER BY ISNULL(Next_Contact_Date, '2079-06-05T23:59:00')

If no legitimate records will have that date.

If this is not an assumption you fancy relying on a more robust option is sorting on two columns.

ORDER BY CASE WHEN Next_Contact_Date IS NULL THEN 1 ELSE 0 END, Next_Contact_Date

Both of the above suggestions are not able to use an index to avoid a sort however and give similar looking plans.

One other possibility if such an index exists is

SELECT 1 AS Grp, Next_Contact_Date 
FROM T 
WHERE Next_Contact_Date IS NOT NULL
UNION ALL
SELECT 2 AS Grp, Next_Contact_Date 
FROM T 
WHERE Next_Contact_Date IS NULL
ORDER BY Grp, Next_Contact_Date

According to Itzik Ben-Gan, author of T-SQL Fundamentals for MS SQL Server 2012, "By default, SQL Server sorts NULL marks before non-NULL values. To get NULL marks to sort last, you can use a CASE expression that returns 1 when the" Next_Contact_Date column is NULL, "and 0 when it is not NULL. Non-NULL marks get 0 back from the expression; therefore, they sort before NULL marks (which get 1). This CASE expression is used as the first sort column." The Next_Contact_Date column "should be specified as the second sort column. This way, non-NULL marks sort correctly among themselves." Here is the solution query for your example for MS SQL Server 2012 (and SQL Server 2014):

ORDER BY 
   CASE 
        WHEN Next_Contact_Date IS NULL THEN 1
        ELSE 0
   END, Next_Contact_Date;

Equivalent code using IIF syntax:

ORDER BY 
   IIF(Next_Contact_Date IS NULL, 1, 0),
   Next_Contact_Date;

order by -cast([Next_Contact_Date] as bigint) desc

If your SQL doesn't support NULLS FIRST or NULLS LAST, the simplest way to do this is to use the value IS NULL expression:

ORDER BY Next_Contact_Date IS NULL, Next_Contact_Date

to put the nulls at the end (NULLS LAST) or

ORDER BY Next_Contact_Date IS NOT NULL, Next_Contact_Date

to put the nulls at the front. This doesn't require knowing the type of the column and is easier to read than the CASE expression.

EDIT: Alas, while this works in other SQL implementations like PostgreSQL and MySQL, it doesn't work in MS SQL Server. I didn't have a SQL Server to test against and relied on Microsoft's documentation and testing with other SQL implementations. According to Microsoft, value IS NULL is an expression that should be usable just like any other expression. And ORDER BY is supposed to take expressions just like any other statement that takes an expression. But it doesn't actually work.

The best solution for SQL Server therefore appears to be the CASE expression.