Getting a conversion failed when converting the varchar value 'string' to datatype int

Question

I'm trying to print 'no data' for the null values in the tavg int datatype column but I keep getting a conversion failed error in the presence of the "then sum((tmin+tmax)/2)" portion. Any help?

    select
    Case 
    when (tavg > -59  and tmin is NULL and tmax is NULL ) then tavg
    when (tavg > -59 and tmin is NULL and tmax > -59) then tavg
    when (tavg is null and tmin >-59 and tmax > -59) then sum((tmin+tmax)/2)
    when (tavg is null and tmin is null and tmax is null) then 'No data'
    else cast(tavg as varchar(50)) end as 'avg_temp',
    case 
    when tmin is null then 'No data' else cast(tmin as varchar(50)) end as 'Minimum Temperature',

artm · Accepted Answer · 2017-03-14 06:30:52Z

4

In a case when all return types should be the same otherwise SQL will try to cast all return types to the highest precedence. The error you are getting is becuase SQL is trying to convert 'No Data' to int (higher precedence). If you return varchar from all cases it should work:

select
    Case 
    when (tavg > -59  and tmin is NULL and tmax is NULL ) then cast(tavg as varchar(50))
    when (tavg > -59 and tmin is NULL and tmax > -59) then cast(tavg as varchar(50))
    when (tavg is null and tmin >-59 and tmax > -59) then cast(sum((tmin+tmax)/2) as varchar(50))
    when (tavg is null and tmin is null and tmax is null) then 'No data'
    else cast(tavg as varchar(50)) end as 'avg_temp',
    case 
    when tmin is null then 'No data' else cast(tmin as varchar(50)) end as 'Minimum Temperature',

Data Type Precedence (Transact-SQL)

edited Mar 14, 2017 at 6:30

answered Mar 14, 2017 at 5:33

artm

8,5943 gold badges28 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Getting a conversion failed when converting the varchar value 'string' to datatype int

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related