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

by

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