SQL Server case/when type conversion problem

In my last project I wrote a query where I have to sort data based on the user input but each column in table has different data type. So to meet with this type of requirement I have used case..when block of the sql server.


Problem: Sample query

DECLARE @sortby Varchar(10)
Set @sortby = 'A1'

Select String1,String2,DateCol
from (
select 'A1','B1', GetDateTime()
union
select 'A2','B2', GetDateTime()
) As d(String1,String2,DateCol)
Order by
Case
When @sortby = 'A1' then String2
When @sortby = 'B1' then String1
When @sortby = 'Date' then Date1
End

there is not syntax error when you check for the syntax error but when execute code you find there is error

ERROR:Conversion failed when converting datetime from character string.

the problem here is first two having databype Varchar and last one having datatype DateTime. So when you executing its found that one branch having datetime so its gives error even its not executing that.

Solution

First Solution
So to avoid this problem you require to convert dateTime to the string

DECLARE @sortby Varchar(10)
Set @sortby = 'A1'
Select String1,String2,DateCol
from (
select 'A1','B1', GetDateTime()
union
select 'A2','B2', GetDateTime()
) As d(String1,String2,DateCol)
Order by
Case
When @sortby = 'A1' then String2
When @sortby = 'B1' then String1
When @sortby = 'Date' then Cast(Date1 as varchar(20))
End


Second Solution
Divide the Case when in multiple statement

DECLARE @sortby Varchar(10)
Set @sortby = 'A1'

Select String1,String2,DateCol
from (
select 'A1','B1', GetDateTime()
union
select 'A2','B2', GetDateTime()
) As d(String1,String2,DateCol)
Order by
Case When @sortby = 'A1' then String2 End,
Case When @sortby = 'B1' then String1 End,
Case When @sortby = 'Date' then Date1 End

Comments

Popular posts from this blog

The model backing the 'MyDBContext' context has changed since the database was created. Either manually delete/update the database, or call Database.SetInitializer with an IDatabaseInitializer instance. For example, the DropCreateDatabaseIfModelChanges strategy will automatically delete and recreate the database, and optionally seed it with new data.

How can I get a value of a property from an anonymous type (C#)

Check/Uncheck all items in a CheckBoxList using Javascript