Sql-server – SQL Server ORDER BY date and nulls last

sql serversql-server-2005tsql

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.

Best Answer

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.

enter image description here

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

Plan

Syntax:

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES

Example:

ALTER TABLE SomeTable
        ADD SomeCol Bit NULL --Or NOT NULL.
 CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
    DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.

Notes:

Optional Constraint Name:
If you leave out CONSTRAINT D_SomeTable_SomeCol then SQL Server will autogenerate
a Default-Contraint with a funny Name like: DF__SomeTa__SomeC__4FB7FEF6

Optional With-Values Statement:
The WITH VALUES is only needed when your Column is Nullable
and you want the Default Value used for Existing Records.
If your Column is NOT NULL, then it will automatically use the Default Value
for all Existing Records, whether you specify WITH VALUES or not.

How Inserts work with a Default-Constraint:
If you insert a Record into SomeTable and do not Specify SomeCol's value, then it will Default to 0.
If you insert a Record and Specify SomeCol's value as NULL (and your column allows nulls),
then the Default-Constraint will not be used and NULL will be inserted as the Value.

Notes were based on everyone's great feedback below.
Special Thanks to:
@Yatrix, @WalterStabosz, @YahooSerious, and @StackMan for their Comments.

Sql – How to return only the Date from a SQL Server DateTime datatype

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))

for example

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

gives me

2008-09-22 00:00:00.000

Pros:

No varchar<->datetime conversions required
No need to think about locale

Best Answer

Related Solutions

Sql – Add a column with a default value to an existing table in SQL Server

Syntax:

Example:

Notes:

Sql – How to return only the Date from a SQL Server DateTime datatype

Related Topic