Previously jokiz bloged about this one, I'd like to add further.
The same issue cropped up again in the forums although not the exactly the same as jokiz's experience, this one could really give you hours of head scratching. Previously I was amazed that dynamic ORDER was possible with T-SQL without resorting to dynamic T-Sql using CASE statements:
SELECT p.ProductId -- int
,p.ProductName -- varchar
,p.EncodedDate -- datetime
,p.IsActive -- bit
FROM dbo.Product p
ORDER BY CASE @sortBy
WHEN 'Id' THEN p.ProductId
WHEN 'Date' THEN p.EncodedDate
ELSE p.ProductName END
The following will work as long as the @sortBy variable is either "Id" or "Date", once it is neither the engine will raise an "Error converting varchar to datetime." The reason is that the CASE statement has to return an undetermined datatype, it does an implicit conversion according to data type precedence; since DateTime is of higher precedence than varchar, the engine will try to cast the varchar to DateTime hence the error.
A hack would be to cast all the columns to be returned as varchar so it won't give that error. However, you'd have to pad integers in order for it be sorted correctly (ie: converting integers(1,2,10) to varchar will give you (1,10,2), so it should be padded first 0001,0002,0010). This can get really ugly and slow. With this kind of scenario and many criterias, the best way would be to resort to dynamic T-Sql.
On a side note, Sql Server also will implicitly cast statement(s) with operators:
SELECT columns
FROM dbo.table001 t
WHERE t.FileNo = 12345 -- Where FileNo is of Varchar with 0 pads(ie. 0012345)
Assuming there is no FileNo like 'X012345', the query will be parsed and executed without errors. Of course it woud be prudent to explicitly CAST it, just in case we'll have heterogenous data in the future.
Hope this helps!