sql - when use the case in order by then it give the error message “conversion failed” -

i have used case in order when complaint status id = 1 sort data complaint date , complaint status id = 2 sort data resolved on date , when complaint status id = 3 sort data resolved on date

but when complaint status id = 3 give error message:

conversion failed when converting date , / or time form character string

order in query

order case @complaintstatusid when 1 a.complaintdate                                   when 2 a.resolveddate                                   when 3 a.resolveddate                                   else 1 end desc 

you projecting 1 of 3 dates, or integer case when statement - won't fly - returns case when must of same type. i'm not sure trying 1, example:

select * sometable order      case @complaintstatusid          when 1 a.complaintdate         when 2 a.resolveddate         when 3 a.resolveddate         else current_timestamp -- same type others     end desc 

edit

i'm guessing 1 desc mean ordering desc first column (which of different type, such *char, hence error). can use two column orderings, mutually exclusive eachother:

select * sometable order      case @complaintstatusid          when 1 a.complaintdate         when 2 a.resolveddate         when 3 a.resolveddate     end desc,     case when @complaintstatusid < 1 or @complaintstatusid > 3        mycol -- first column in select list     end desc; 

afaik you'll need explicitly name column 1.

sqlfiddle here