Conversion failed when converting the varchar valu

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Conversion failed when converting the varchar valu

Author

Topic

gagani
Posting Yak Master

112 Posts

Posted - 2013-05-30 : 11:02:01

I am getting the error: 'Conversion failed when converting the varchar value 'R' to data type int' when I try to run the following query

select case when 0 between startmonth and endmonth then 'R' else 0 end as units0,
case when 1 between startmonth and endmonth then 'R' else 0 end as units1,
case when 2 between startmonth and endmonth then 'R' else 0 end as units2,
case when 3 between startmonth and endmonth then 'R' else 0 end as units3,
case when 4 between startmonth and endmonth then 'R' else 0 end as units4,
case when 5 between startmonth and endmonth then 'R' else 0 end as units5,
case when 6 between startmonth and endmonth then 'R' else 0 end as units6,
case when 7 between startmonth and endmonth then 'R' else 0 end as units7,
case when 8 between startmonth and endmonth then 'R' else 0 end as units8,
case when 9 between startmonth and endmonth then 'R' else 0 end as units9,
case when 10 between startmonth and endmonth then 'R' else 0 end as units10,
case when 11 between startmonth and endmonth then 'R' else 0 end as units11
from tbl_ipl
where iplyear = 2013 and iplbandid = 65 and pupilid = 622347

startmonth and endmonth in the above query are integer types, so it's throwing the error

But I am in need of the following output

units0 units1 units2 units3 units4 units5 units6 units7 units8
R R R R R R 0 0 0
units9 units10 units11
0 0 0

Could anyone make changes to the query for the required output

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-30 : 11:10:01

Put all the zeros in the "ELSE 0" in single quotes:

SELECT  CASE WHEN 0 BETWEEN startmonth AND endmonth THEN 'R' ELSE '0' END AS units0 ,
        CASE WHEN 1 BETWEEN startmonth AND endmonth THEN 'R' ELSE '0' END AS units1 ,
....