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

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.

  • Database design help with varying schemas
  • Where clause based on value from function
  • SSRS report subscription ended with an error
  • How to deploy ssrs report on production server
  • Preventing Conditional INSERT/UPDATE Race Condition in MS-SQL
  • How to determine the field value which can not convert to (decimal, float,int) in SQL Server
  • 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?

    2 Solutions collect form web for “Conversion failed when converting date and/or time from character string”

    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.

    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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.