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

Question

I'm getting a conversion error when ordering in a complex query. I've distilled it down to the following:

DECLARE @sortBy VARCHAR(25) = 'Route'
DECLARE @sortDirection VARCHAR(25) = 'DESC'

select * from dbo.headers
order by case when @sortDirection = 'DESC' then
    case
        when @sortBy = 'Route' then route
        when @sortBy = 'DateCompleted' then dateCompleted
    end
end desc

Oddly works fine if I change the sortBy variable to 'DateCompleted'.

Would appreciate some input here as I've been trying to figure it out for a few hours at least.

I've tried sorting by the date column itself, which works fine. But I want to order by other things too, resulting in a conversion error. The dateCompleted column is a nullable datetime.

Answer 1

It might be because it is trying to convert a "route" to a DATE or DATETIME or whatever the datatype for "dateCompleted" is.

You could try CONVERT(NVARCHAR(30), dateCompleted, 121)