Conversion failed when converting date and/or time from character string

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)

Leave a Comment