How do I use both the varchar and Datetime datatypes in a CASE statement using Sql Server 2008?

I have an stored procedure and I am using this variable i.e. @orderBy.

Here is my code be开发者_如何学Golow:

ORDER BY
        CASE 
            WHEN @orderBy = 'ClientName' THEN c.LastName
            WHEN @orderBy = 'EmployerName' THEN cda.Name
            WHEN @orderBy = 'EmploymentDate' THEN cda.StartDate
        END

The problem is when I'm using the @orderBy variable, it is a varchar and it is giving error on the third line:

Conversion failed when converting character string to smalldatetime data type. Working fine if I comment third line i.e. WHEN @orderBy = 'EmploymentDate' THEN cda.StartDate

How do I get past this datatype conversion error?

Thanks in advance.

You have to separate the datatypes

ORDER BY
    CASE 
        WHEN @orderBy = 'ClientName' THEN c.LastName
        WHEN @orderBy = 'EmployerName' THEN cda.Name
        ELSE ''
    END,
    CASE 
        WHEN @orderBy = 'EmploymentDate' THEN cda.StartDate
        ELSE '19000101'
    END

Or convert smalldatetime to varchar but keep sort logic

ORDER BY
    CASE 
        WHEN @orderBy = 'ClientName' THEN c.LastName
        WHEN @orderBy = 'EmployerName' THEN cda.Name
        --gives "yyyy-mm-ddThh:mm:ss.mmm "
        WHEN @orderBy = 'EmploymentDate' THEN CONVERT(varchar(30), cda.StartDate, 121)
    END

Or have 3 separate CASEs, as per AlexK's answer

You can use a case-per-column to remove the mixed types;

ORDER BY
    CASE WHEN @orderBy = 'ClientName' THEN c.LastName END,
    CASE WHEN @orderBy = 'EmployerName' THEN cda.Name END,
    CASE WHEN @orderBy = 'EmploymentDate' THEN cda.StartDate END

try replace

WHEN @orderBy = 'EmploymentDate' THEN cda.StartDate

with

WHEN @orderBy = 'EmploymentDate' THEN cast(cda.StartDate as VarChar)