order by

There is a field called field1 in a table called table1
Field1 is varchar(50)
Data inside field1 is such as:

3T
apr 2007
feb 2001
jan 2001
dec 1999
...

I am writing a sql query to return the data so that the field1 is sorted:
ie:

3T
dec 1999
jan 2001
feb 2001
apr 2007
...

This is what I have in the order by clause:

cast('01-' + replace(field1, ' ', '-') as datetime)

The problem is if there is something else such as '3T' or another text inside this field1.
Should there be a case statement inside the oprder by clause?

Please note that if there is any text which does not seem to be a date then it should appear first in the list as shown in the example above.
Thanks

Code Snippet

select'3T'as data

into #t

union allselect'feb 2001'

union allselect'apr 2007'

union allselect'dec 1999'

union allselect'jan 2001'

selectcaseisdate(data)when 1 thenconvert(varchar(10),cast(data asdatetime), 102)else data end

from #t

orderbyisdate(data),

caseisdate(data)when 1 thenconvert(varchar(10),cast(data asdatetime), 102)else data end

|||

Use the following query..

Code Snippet

Create Table #data (

[Col] Varchar(100)

);

Insert Into #data Values('apr 2007');

Insert Into #data Values('feb 2001');

Insert Into #data Values('jan 2001');

Insert Into #data Values('dec 1999');

Insert Into #data Values('Americas');

Insert Into #data Values('APJ');

Insert Into #data Values('EMEA');

Insert Into #data Values('1982');

Insert Into #data Values('AJP09837');

Select * From #data Order By

Case When Isdate(Col)=1 Then 1 End,

Case When IsDate(Col)=1 Then cast(Col as datetime) End,

Col

drop table #data