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)
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.
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.
I tried with the above trick and its not working for me. pls advice.
I agree with Andrez and Tiago.
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.
Above solution does not work even if the field is “Date”