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

SQL Server - Formatted Date and Alias Name in ORDER BY Clause datetime 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

SQL Server - Formatted Date and Alias Name in ORDER BY Clause alias_result1

Solarwinds

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

SQL Server - Formatted Date and Alias Name in ORDER BY Clause alias_result2

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

SQL Server - Formatted Date and Alias Name in ORDER BY Clause alias_result3

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)

Solarwinds
, , , ,
Previous Post
SQL SERVER – Selecting Random n Rows from a Table
Next Post
SQL SERVER – Configuration Manager Error: The Server Threw an Exception [0x80010105]

Related Posts

5 Comments. Leave new

  • You can also qualify ‘dob’ with the table name/alias. It will sort by the unconverted value, pulling from the table instead of the select clause.

    Reply
  • Hi Dave,
    There is no need to change the alias name.
    I always use a table alias, even if I just have a table in the from clause.
    That way you can order by .dbo.

    Regards,
    Tiago.

    Reply
  • Shantilal Suthar
    September 28, 2018 5:14 pm

    Hi Pinal,

    I tried with the above trick and its not working for me. pls advice.

    I agree with Andrez and Tiago.

    Thanks.
    Shantilal

    Reply
  • Hi Pinal,

    I agree with Andre and Tiago. There is no need to change the column alias instead give table alias or table name qualified by the column name in the ORDER BY to retrieve the expected output.

    Thanks,
    Srini

    Reply
  • Above solution does not work even if the field is “Date”

    Reply

Leave a Reply

Menu