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

sql, sql-server, sql-server-2008, t-sql

Solution

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.

Problem

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?

Original source