Post reply

Case with Int and Varchar

  • November 18, 2014 at 1:05 pm

    #295231

    I am trying a case statement but get the error : "Conversion failed when converting the varchar value 'No Data Available' to data type int."

    Case statement is

    Case when DATEDIFF(Day, Time_Stamp, getdate()) < = 1 And Status = 'Red' then SUM(RedCount) Else 'No Data Available'End as CurrentRed

    I don't want to bring in else = 0 as there could be a data 0 in it. How can I fix this?

  • November 18, 2014 at 1:10 pm

    #1759388

    sharonsql2013 (11/18/2014)

    I am trying a case statement but get the error : "Conversion failed when converting the varchar value 'No Data Available' to data type int."

    Case statement is

    Case when DATEDIFF(Day, Time_Stamp, getdate()) < = 1 And Status = 'Red' then SUM(RedCount) Else 'No Data Available'End as CurrentRed

    I don't want to bring in else = 0 as there could be a data 0 in it. How can I fix this?

    Can you use?

    CASE WHEN DATEDIFF(Day, Time_Stamp, getdate()) <= 1 And Status = 'Red' then SUM(RedCount) Else NULL End as CurrentRed

  • November 18, 2014 at 1:40 pm

    #1759394

    The reason is that the two parts of your CASE statement return different data types. SUM returns an integer and 'No data available' returns a varchar. If you can run djj's code above, then you'll confirm it and you can then wrap it in a CONVERT or ISNULL.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • November 18, 2014 at 2:35 pm

    #1759408

    Or you can use CAST/CONVERT around SUM().

    Case when DATEDIFF(Day, Time_Stamp, getdate()) < = 1 And Status = 'Red' then CAST( SUM(RedCount) AS varchar(30)) Else 'No Data Available'End as CurrentRed

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • November 19, 2014 at 12:11 pm

    #1759608

    Thank y'all

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply