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

CONVERT function can be used to change the format of DATE to various formats like MM/dd/yyy, dd/MM/yyy, yyy/MM/dd, etc. But have you ever noticed that this will produce resultset with a different order if the alias name is used in ORDER BY Clause? Well, I just learned about the formatted date from my recent consulting engagement Comprehensive Database Performance Health Check.

Let us create the following data set

USE tempdb
GO
CREATE TABLE customer_master
(
customer_id INT,
customer_name VARCHAR(100),
dob DATETIME
);

INSERT INTO customer_master (customer_id,customer_name,dob)
SELECT 10001,'Johnson','1992-10-19' UNION ALL
SELECT 10002,'Peter','1957-03-22' UNION ALL
SELECT 10003,'Clara','1981-12-15' UNION ALL
SELECT 10004,'Medalyn','1966-02-18' UNION ALL
SELECT 10005,'Sara','1959-10-16' ;

Now Order the resultset by dob column which is of DateTime datatype.

SELECT customer_id,customer_name,dob
FROM customer_master
ORDER BY dob

As you see the result is ascending order of the column dob

Let us use the same SELECT statement by formatting the dob column to MM/dd/yyyy format in the SELECT column list

SELECT customer_id,customer_name,CONVERT(VARCHAR(10),dob,101) AS dob
FROM customer_master
ORDER BY dob

As shown below, If you see the result, you can observe that the result is not actually based on DOB as date values but dob as formatted VARCHAR value

The reason is the name dob is used as alias name for the formatted date values and when referenced in ORDER BY clause it sorts the resultset based on VARCHAR values of formatted dates and not based on an original DATE value

How do we solve this problem?

Simply use a different alias name

SELECT customer_id,customer_name,CONVERT(VARCHAR(10),dob,101) AS dob_new
FROM CUSTOMER_MASTER
ORDER BY DOB

Now the result is ordered by DATE value and not by formatted VARCHAR value.

How many of you know this already?

Reference: Pinal Dave (https://blog.sqlauthority.com)