CODEWITHSUNDEEP
sql-servert-sqlsql-order-by
Sukhjeevan
Sukhjeevan

Reputation: 3156

Order By problem in T-Sql

I am getting error in following T-Sql:-

DECLARE @sortby VARCHAR(10)
SET @sortby='A1'

SELECT String1, String2, Date1  
FROM (
        SELECT 'A1', 'B1', CONVERT(datetime,'1 July 2010 00:01:00')
        UNION ALL
        SELECT 'A2', 'B2', CONVERT(datetime,'2 July 2010 00:02:00')
        ) AS a(String1,String2,Date1)
ORDER BY
        CASE 
                WHEN @sortby = 'A1' THEN String2 
                WHEN @sortby = 'B1' THEN String1
                WHEN @sortby = 'Date1' THEN Date1
        END

ERROR:Conversion failed when converting datetime from character string.

Its working fine only if I SET @sortby='Date1'.

Can anybody tell me if there is any syntax mistake?

Upvotes: 2

Views: 537

Answers (4)

Paul Keister
Paul Keister

Reputation: 13077

You must cast the last value as varchar, otherwise the type for the order by clause is interpreted as a date:

DECLARE @sortby VARCHAR(10)
SET @sortby='A1'

SELECT String1, String2, Date1  
FROM (
        SELECT 'A1', 'B1', CONVERT(datetime,'1 July 2010 00:01:00')
        UNION ALL
        SELECT 'A2', 'B2', CONVERT(datetime,'2 July 2010 00:02:00')
        ) AS a(String1,String2,Date1)
ORDER BY
        CASE 
                WHEN @sortby = 'A1' THEN String2 
                WHEN @sortby = 'B1' THEN String1
                WHEN @sortby = 'Date1' THEN CONVERT(VARCHAR(10), Date1, 112)
        END

Upvotes: 0

Pranay Rana
Pranay Rana

Reputation: 176896

When you write a case/when statement in T-SQL, it's critically important that you cause all return values to have the same data type. If you don't do this, you open yourself up to conversion problems. This problem can occur even if some branches of the code are never executed.

so something as below will not work 

DECLARE @DATA VARCHAR(20)

SET @DATA = ''

SELECT CASE WHEN @DATA IS NULL THEN NULL
            WHEN @DATA = ''    THEN 'Data is empty'
            WHEN 0=1           THEN 1
            END

More with solution : http://pranayamr.blogspot.com/2010/11/sql-server-casewhen-return-type.html

Upvotes: 1

Anthony Faull
Anthony Faull

Reputation: 17957

Separate your column names with commas in the order-by clause.

ORDER BY 
        CASE WHEN @sortby = 'A1' THEN String2 END,
        CASE WHEN @sortby = 'B1' THEN String1 END,
        CASE WHEN @sortby = 'Date1' THEN Date1 END

Upvotes: 1

Harendra
Harendra

Reputation: 249

Try this..

DECLARE @sortby VARCHAR(10) 
SET @sortby='Date1' 

SELECT String1, String2, Date1   
FROM ( 
        SELECT 'A1', 'B1', CONVERT(datetime,'1 July 2010 00:01:00') 
        UNION ALL 
        SELECT 'A2', 'B2', CONVERT(datetime,'2 July 2010 00:02:00') 
        ) AS a(String1,String2,Date1) 
ORDER BY 
        CASE  
                WHEN @sortby = 'A1' THEN String2  
                WHEN @sortby = 'B1' THEN String1 
                WHEN @sortby = 'Date1' THEN convert(datetime,cast(Date1 as varchar(20))) 
        END

Upvotes: 0

Related Questions