SQL Sort by based off of CASE - Not working

Question

Not sure why this isn't working:

ORDER BY a.QuestionTitle

When by itself it works. But below, if SortBy is equal to 4...

ORDER BY 
CASE WHEN (@SortBy = 1) THEN a.Date_Created 
     WHEN (@SortBy = 2) THEN a.Date_Created
     WHEN (@SortBy = 3) THEN a.Date_Created
     WHEN (@SortBy = 4) THEN a.QuestionTitle 
 END DESC

I get this error: Conversion failed when converting date and/or time from character string.

Thank you very much in advance for your help.

Don't feel stupid: I've hit this problem before. The solution is non-obvious. — Mitch Wheat, Aug 25 '13 at 04:13

score 1 · Accepted Answer · edited May 23 '17 at 12:28

1

Use multiple CASE statements:

ORDER BY 
    CASE WHEN @SortBy = 1 THEN a.Date_Created END DESC,
    CASE WHEN @SortBy = 2 THEN a.Date_Created END DESC,
    CASE WHEN @SortBy = 3 THEN a.Date_Created END DESC,
    CASE WHEN @SortBy = 4 THEN a.QuestionTitle END DESC

[A quick search would have found this as first hit.]

edited May 23 '17 at 12:28

Community

1
1

answered Aug 25 '13 at 04:11

Mitch Wheat

295,962
43
465
541

score 1 · Answer 2 · answered Aug 25 '13 at 05:09

The problem is that you can't return multiple types from a case statement. All possible values have to be of the same type. Because of precedence, there is an implicit conversion of a.QuestionTitle to either date or datetime (depending upon the type of a.date_created).

You have two possible solutions, either convert the dates to a string or use a separate case statement for a.QuestionTitle.

Case when @sortby in(1,2,3) then a.date_created end desc,
Case when @sortby=4 then a.QuestionTitle end desc

Case when @sortby in(1,2,3) then convert(varchar(22), a.date_created, 121) 
     when @sortBy=4 the a.questionTitle end desc

SQL Sort by based off of CASE - Not working

2 Answers2