ORDER BY DATE showing NULLS first then most recent dates

Question

I have a stored procedure which executes a select statement. I would like my results ordered by a date field and display all records with NULL dates first and then the most recent dates.

The statement looks like this:

SELECT a,b,c,[Submission Date]
FROM someView
ORDER BY [Submission Date] ASC

Now this will display all records with NULL Submission Dates first, but when I get to rows that have date values in them, they are not the most recent dates in the view.

If I replace ASC with DESC, then I get the dates in the the order I want, but the NULL values are at the bottom of my result set.

Is there any way to structure my query so that I can display the null values at the top and then when there are date values, to order them descending (most recent to oldest)?

Answer 1

try

SELECT a,b,c,[Submission Date]
FROM someView
ORDER BY isnull([Submission Date],cast('2079/01/01' as datetime)) ASC

Answer 2

You can do something like this put the NULL's at the bottom:

ORDER BY [Submission Date] IS NULL DESC, [Submission Date] ASC

Answer 3

try this

SELECT a,b,c,[Submission Date] FROM someView ORDER BY isnull([Submission Date] ,cast('1770/01/01' as datetime)) ASC

Answer 4

@Chris, you almost have it.

ORDER BY (CASE WHEN [Submission Date] IS NULL THEN 1 ELSE 0 END) DESC, 
         [Submission Date] DESC

[Edit: #Eppz asked me to tweak the code above as currently shown]

I personally prefer this a lot better than creating "magic numbers". Magic numbers are almost always a problem waiting to happen.

Answer 5

Standard SQL (ISO/IEC 9075-2:2003 or later - 2008) provides for:

ORDER BY SomeColumn NULLS FIRST

Most DBMS do not actually support this yet, AFAIK.