Friday, August 16, 2013

"Conversion failed when converting date and/or time from character string." when ORDER BY CASE

Problem
My query looks like this
ORDER BY
CASE @OrderBy
       WHEN 'A' THEN A
       WHEN 'B' THEN B
      etc...
END
DESC

I was getting the "Conversion failed when converting date and/or time from character string."when @OrderBy was set to certain values.

Solution
You need to use a separate case for each possible OrderBy. So I had to turn the query above into

ORDER BY 
    CASE WHEN @OrderBy = 'A' THEN A END DESC,
    CASE WHEN @OrderBy = 'B' THEN B END DESC


Reference
1. http://stackoverflow.com/questions/10002401/conversion-failed-when-converting-date-and-or-time-from-character-string 

No comments:

Post a Comment

There was an error in this gadget