SQL Server - Order by date substring - Asked By jason barry on 30-Apr-13 10:46 PM

Hi, I have a field called VALID_FROM_DATE which includes results such as
01 Apr 08
06 Apr 13
01 Aug 14

I am trying to order by this field which converts the date to this format on the fly.

Is there a way to order by so it appears as such

01 Aug 14
06 Apr 13
01 Apr 08

This needs to be in an order by so the latest dates come first. the typical ORDER BY does not order the dates correcty. Thankyou

Robbe Morris replied to jason barry on 01-May-13 05:16 PM

Storing dates as string is a cardinal sin in relational databases. You are sorting strings not dates and this will forever haunt your application. If you are smart, you'll either change the column data type or add another that stores the data properly for needs like this.

Sandeep Mittal replied to jason barry on 13-May-13 08:09 AM

declare @tab table(VALID_FROM_DATE varchar(20))

insert into @tab

select '01 Apr 08' union all

select '06 Apr 13' union all

select '01 Aug 14'

select * from @tab

order by CAST(VALID_FROM_DATE as date) desc