Problem
When I execute a SQL query I’m getting the following error:
Conversion failed when converting date and/or time from character string.
The query is using ORDER BY with CASE with a passed in parameter called @OrderBy.
DECLARE @OrderBy nvarchar(20) = 'CreatedBy'
SELECT * FROM Articles
ORDER BY
CASE
WHEN @OrderBy = 'CreatedDateTime' THEN CreatedDateTime
WHEN @OrderBy = 'CreatedBy' THEN CreatedBy
END
Code language: SQL (Structured Query Language) (sql)
Solution
Use a separate CASE statement for each possible value (instead of one CASE with multiple WHENs).
DECLARE @OrderBy nvarchar(20) = 'CreatedBy'
SELECT * FROM Articles
ORDER BY
CASE WHEN @OrderBy = 'CreatedDateTime' THEN CreatedDateTime END,
CASE WHEN @OrderBy = 'CreatedBy' THEN CreatedBy END
Code language: SQL (Structured Query Language) (sql)