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

by

Problem

When you’re working with date/times in SQL and want to put a date column in ORDER BY with a CASE statement, you may run into the following error:

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

Here’s an example of a query using ORDER BY with a CASE that produces the error:

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

To eliminate this error, use a separate CASE statement for each possible value (instead of one CASE with multiple WHENs). Here’s an example:

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)

Related Articles