SQL Server – Formatted Date and Alias Name in ORDER BY Clause

A formatted date alias can change ORDER BY from a date sort into a text sort. I encountered this during a Comprehensive Database Performance Health Check. The query still ran successfully, but the oldest customer was no longer first.

Create a small sample

Run these examples in one fresh SQL Server query session. The local temporary table keeps the demonstration separate from your application tables. The unseparated date literals avoid language-dependent day/month interpretation.

CREATE TABLE #CustomerSort
(
    CUSTOMER_ID int NOT NULL PRIMARY KEY,
    CUSTOMER_NAME varchar(100) NOT NULL,
    DOB datetime NOT NULL
);
INSERT INTO #CustomerSort (CUSTOMER_ID, CUSTOMER_NAME, DOB)
VALUES (10001, 'JOHNSON', '19921019'),
       (10002, 'PETER',   '19570322'),
       (10003, 'CLARA',   '19811215'),
       (10004, 'MEDALYN', '19660218'),
       (10005, 'SARA',    '19591016');

First, sort the date value

SELECT c.CUSTOMER_ID, c.CUSTOMER_NAME, c.DOB
FROM #CustomerSort AS c
ORDER BY c.DOB, c.CUSTOMER_ID;

The order is Peter, Sara, Medalyn, Clara and Johnson, from 1957 through 1992. These are the original sample customers. The retained result images show that same data and ordering.

Now format the date and reuse its name

SELECT c.CUSTOMER_ID,
       c.CUSTOMER_NAME,
       CONVERT(varchar(10), c.DOB, 101) AS DOB
FROM #CustomerSort AS c
ORDER BY DOB, c.CUSTOMER_ID;

Style 101 displays the value as mm/dd/yyyy. The conversion also changes the selected expression to varchar(10). The unqualified ORDER BY DOB refers to the selected alias, so this query sorts the formatted strings.

Medalyn now appears before Peter because 02/18/1966 sorts before 03/22/1957 as text. The month appears before the year in this display format. That order is not chronological.

Format for display, sort by the source column

SELECT c.CUSTOMER_ID,
       c.CUSTOMER_NAME,
       CONVERT(varchar(10), c.DOB, 101) AS DOB_NEW
FROM #CustomerSort AS c
ORDER BY c.DOB, c.CUSTOMER_ID;

The distinct alias DOB_NEW makes the output easier to read. More importantly, c.DOB explicitly selects the underlying datetime as the sort key. You could keep the display alias DOB and still sort correctly with ORDER BY c.DOB.

The customer ID is a stable tie-breaker when two customers have the same birth date. Renaming a display alias alone does not help if ORDER BY still points to that formatted expression.

Keep the data type until presentation

Store dates in an appropriate date/time type. Where practical, return that type to the application and format it there. If SQL must provide display text, keep a clear separation between the display expression and the sort key.

This example uses a simple SELECT. DISTINCT and combined queries such as UNION have additional ORDER BY restrictions, so do not assume that every query can sort by an unselected source expression.

After reviewing the three results, remove the temporary table:

DROP TABLE #CustomerSort;

A formatted date is not the original date value, it is text created for display.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.