SQL order by

I need to order a table conditioned by date and i cant do it!! :(

i have a field (codigo) that if the year of the date(passed by parameter) is less than 2010 then it is composed like this : "FAC-00123-10", then i nedd to order by this "0开发者_Python百科0123"...

Otherwise, if the year code is bigger than 2010, the field (Codigo) is created like this "FT 11/123" and then i need to order by this "123"

How can i do this?!

You can use a case when statement to decide what to order by, this uses a simple substring but in reality this probably isnt good enough and you may need to parse the bit you are interested in a little bit better..

select * from table order by 
   case when DatePart(year,@date) < 2010 then substring(codigo,4,5)
        else substring(condigo,3,2) end