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

Question

I have a fairly simple pagination query used to get rows from a table

ALTER PROCEDURE mytable.[news_editor_paginate]
    @count int,
    @start int,
    @orderby int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT TOP (@count) * FROM 
    (  
        SELECT news_edits.*,
        ROW_NUMBER() OVER (
            ORDER BY CASE 
                WHEN @orderby = 0 THEN news_edits.[time]
                WHEN @orderby = 1 THEN news_edits.lastedit
                WHEN @orderby = 2 THEN news_edits.title
                END
            DESC
        ) AS num
        FROM news_edits
    ) AS a
    WHERE num > @start
END

The @orderby parameter decides which column the results should be ordered by.

news_edit.[time] and news_edits.lastedit are both datetime fields. But news_edits.title is a varchar field.

The query runs fine for both the datetime fields but when @orderby = 2 I get the following error:

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

The problem I'm having is that I'm not trying to convert anything?

Michael Fredrickson Michael Fredrickson · Accepted Answer · 2012-04-03T22:33:19

You'll need to divide your ORDER BY into multiple CASE statements:

ORDER BY 
    CASE WHEN @orderby = 0 THEN news_edits.[time] END DESC,
    CASE WHEN @orderby = 1 THEN news_edits.lastedit END DESC,
    CASE WHEN @orderby = 2 THEN news_edits.title END DESC

This is because single CASE statement requires that all branches have compatible data types. Since your character string in one CASE can't be converted to the date time returned from another CASE, you get the conversion error.

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

2 Answers