SQL CASE INT Conversion

I am trying to use CASE to group table contents that include characters and NULL and Blank entries using the following:

SELECT  COUNT(*) AS Clients,
   CASE WHEN t.Diagnosedin = 'Unknown' OR t.Diagnosedin ='' THEN 'Unknown' 
   ELSE CASE WHEN CAST((CAST(DATENAME(Ye开发者_开发技巧ar,GETDATE()) AS INT)- t.Diagnosedin)AS varchar(5)) =1 THEN 1
   ELSE CASE WHEN CAST((CAST(DATENAME(Year,GETDATE()) AS INT)- t.Diagnosedin)AS varchar(5)) <=2 THEN 2
   ELSE CASE WHEN CAST((CAST(DATENAME(Year,GETDATE()) AS INT)- t.Diagnosedin)AS varchar(5)) <=5 THEN 5
   ELSE CASE WHEN CAST((CAST(DATENAME(Year,GETDATE()) AS INT)- t.Diagnosedin)AS varchar(5)) <=10 THEN 10 
   ELSE CASE WHEN CAST((CAST(DATENAME(Year,GETDATE()) AS INT)- t.Diagnosedin)AS varchar(5))>=10 THEN '+10 '
   END
   END
   END
   END
   END
   END AS Years

I get the following error: Conversion failed when converting the varchar value 'Unknown' to data type int., I am using MS SQL 2008. Thanks in advance.

What's happening is that you're mixing literals of different datatypes in the column Years. (i.e. int 5 and varchar Unknown will be in the same resultset). You need to code those literal values as the same datatype.

Try this:

• cast your hardcoded integers to strings/varchar. I've used the STR() function, and used the LRTIM() to ensure that the leading spaces aren't kept.
• use the same CASE

SELECT COUNT(*) AS Clients,
   CASE WHEN t.Diagnosedin = 'Unknown' OR t.Diagnosedin ='' THEN 'Unknown' 
    WHEN CAST((CAST(DATENAME(Year,GETDATE()) AS INT)- t.Diagnosedin)AS varchar(5)) =1 THEN LTRIM(STR(1))
    WHEN CAST((CAST(DATENAME(Year,GETDATE()) AS INT)- t.Diagnosedin)AS varchar(5)) <=2 THEN LTRIM(STR(2))
    WHEN CAST((CAST(DATENAME(Year,GETDATE()) AS INT)- t.Diagnosedin)AS varchar(5)) <=5 THEN LTRIM(STR(5))
    WHEN CAST((CAST(DATENAME(Year,GETDATE()) AS INT)- t.Diagnosedin)AS varchar(5)) <=10 THEN LTRIM(STR(10)) 
    WHEN CAST((CAST(DATENAME(Year,GETDATE()) AS INT)- t.Diagnosedin)AS varchar(5))>=10 THEN '+10 '
END AS Years

The answer is there in front of you. You can only return one data type in a column. Your first WHEN statement establishes Unknown (string) as a value. Subsequent statements deliver integer values. The db engine says I got an integer and I need to stick a string in it and that won't fit.

Options are to add a cast to all the numeric operations to convert them to varchar(n) {as p.campbell just submitted} or to change your Unknown value to something like NULL or an obviously not valid sentinel value like -1.