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

Go To StackoverFlow.com

9

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?

2012-04-03 22:30
by James Hay


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.

2012-04-03 22:33
by Michael Fredrickson
That's it thankyou - James Hay 2012-04-03 23:06


0

As you're not explicitly casting the "order by case..." values, SQL Server infers it's a datetime (according to the type of the first case).

The solution to your problem is to cast the dates in a string format that allows you to order by it, somewhat like 'yyyyMMddhhmmss'. If you do so, all the "order by case..." values will be chars and it will work.

Alternatively, you can have two selects, and choose one of them with an if. The first part of the if for the dates, the second for the title.

2012-04-03 22:40
by JotaBe