CODEWITHSUNDEEP
sqlsql-server-2008
user3324467
user3324467

Reputation: 19

How to sort the datetime value after converting it into a varchar?

In this query i am unable to order by right

select CONVERT(varchar(10), cast(StartDate as date),101) +' - ' + CONVERT(varchar(10), cast(EndDate as date),101) Duration from dbo.Calendar order by CONVERT(DATE, StartDate, 101) desc;

It gives me the following result which is not ordered right mm/dd/yyyy

05/06/2013 - 06/29/2013
01/14/2013 - 04/26/2013
08/27/2012 - 12/15/2012
06/25/2012 - 08/18/2012
04/30/2012 - 06/23/2012
01/09/2012 - 04/23/2012
05/02/2011 - 08/22/2011

Upvotes: 1

Views: 2188

Answers (1)

crthompson
crthompson

Reputation: 15865

Instead of converting your order by to a varchar, leave it as a datetime.

select 
    CONVERT(varchar(10), 
    cast(StartDate as date),101) +' - ' + CONVERT(varchar(10), 
    cast(EndDate as date),101) Duration 
from 
    dbo.Calendar 
order by StartDate desc, EndDate;

Edit:

Added EndDate in your orderby as well, in case there are start dates that are the same

I misread your query initially. You cast StartDate to a DateTime already, so the order is correct.

Adding DESC to your datetime means that the newest or most recent value is first, oldest is last.

Upvotes: 1

Related Questions