Formatted dates , alias name and order by clause

One of my friends told me that when he was trying to order by datetime values, the result is not in proper order.

Consider the following data

create table #orders (order_id int, order_date datetime, order_value decimal(12,2))
insert into #orders (order_id,order_date,order_value)
select 1,'20150107',200 union all
select 2,'20151110',1300 union all
select 3,'20140404',500 union all
select 4,'20150110',2000 union all
select 5,'20150318',450

Suppose you want to show formatted dates and order by the order_date column, you can use the following code

select order_id,convert(varchar(10),order_date,103) as order_date,order_value
from #orders
order by order_date

The following is the result

order_id    order_date order_value
----------- ---------- ------------
3           04/04/2014 500.00
1           07/01/2015 200.00
4           10/01/2015 2000.00
2           10/11/2015 1300.00
5           18/03/2015 450.00

If you look at the result you can notice that the formatted dates are not in proper order although you order it by order_date column which is of datetime datatype. So what is the problem? It is the problem with using same column alias name for formatted dates. If you use different alias name for formatted dates and order by order_date you will get result properly

select order_id,convert(varchar(10),order_date,103) as order_date_formatted,order_value
from #orders
order by order_date

The result is

order_id    order_date_formatted order_value
----------- -------------------- --------------
3           04/04/2014           500.00
1           07/01/2015           200.00
4           10/01/2015           2000.00
5           18/03/2015           450.00
2           10/11/2015           1300.00

Please note that formatting datetime values is not recommended as they become characters and not datetime values so that any datetime related calculations are not possible until you convert them back to datetime values.

If you still want to do format for display purpose, and order it by original datetime value, use different alias name

If do not want to use different alias name, you need to again convert it back to DATETIME data type

select order_id,convert(varchar(10),order_date,103) as order_date,order_value
from #orders
order by convert(datetime,order_date,103)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s