Null Date Values and order by

[Dean Mathews]

How do I order a query by a date field ASC, but have any NULL values
show up last? i.e.

7/1/2003
7/5/2003
7/10/2003
<NULL>
<NULL>


Any help will greatly be appreciated

[Erland Sommarskog]

Re: Null Date Values and order by

Dean Mathews (paradigmis@hot mail.com) writes:[color=blue]
> How do I order a query by a date field ASC, but have any NULL values
> show up last? i.e.
>
> 7/1/2003
> 7/5/2003
> 7/10/2003
><NULL>
><NULL>[/color]

ORDER BY colaesce(date, '99991231')

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Andre Kuyt]

Re: Null Date Values and order by

paradigmis@hotm ail.com (Dean Mathews) wrote in message news:<e32dbacc. 0307240715.5627 9933@posting.go ogle.com>...[color=blue]
> How do I order a query by a date field ASC, but have any NULL values
> show up last? i.e.
>
> 7/1/2003
> 7/5/2003
> 7/10/2003
> <NULL>
> <NULL>
>
>
> Any help will greatly be appreciated[/color]

simply use the following in the ORDER BY Clause
select
datefield
from
table
ORDER BY
ISNULL(datefiel d, '20550101')

(or any other date far larger than the maximum of your resultset)

[Dean Mathews]

Re: Null Date Values and order by

This works great, thanks for all your help

Dean