Post reply

Operand data type varchar is invalid for subtract operator

  • May 5, 2014 at 12:54 am

    #288385

    Hi,

    I am using the below query to sum two columns values and I couldn't sum it.

    I get this error:Operand data type varchar is invalid for subtract operator

    Query:

    Variance = '$ ' + REVERSE(SUBSTRING(REVERSE(CONVERT(VARCHAR,cast(round(isnull(sum(Current_Tear),0),0)as money),1)),4,255)) -

    '$ ' + REVERSE(SUBSTRING(REVERSE(CONVERT(VARCHAR,cast(round(isnull(sum(Previous_Year),0),0)as money),1)),4,255))

  • May 5, 2014 at 1:20 am

    #1710640

    The error message says it all:Operand data type varchar is invalid for subtract operator

    Variance = '$ ' + REVERSE(SUBSTRING(REVERSE(CONVERT(VARCHAR,cast(round(isnull(sum(Current_Tear),0),0)as money),1)),4,255)) - <<<<<< This is your subtract operator

    '$ ' + REVERSE(SUBSTRING(REVERSE(CONVERT(VARCHAR,cast(round(isnull(sum(Previous_Year),0),0)as money),1)),4,255))

    If you want to concatenate the "-" sign use this:

    Variance = '$ ' + REVERSE(SUBSTRING(REVERSE(CONVERT(VARCHAR,cast(round(isnull(sum(Current_Tear),0),0)as money),1)),4,255)) + '-' +

    '$ ' + REVERSE(SUBSTRING(REVERSE(CONVERT(VARCHAR,cast(round(isnull(sum(Previous_Year),0),0)as money),1)),4,255))

    Otherwise, I have no idea what you're trying to do.

    -- Gianluca Sartori

  • May 5, 2014 at 1:20 am

    #1710641

    does the following get anywhere close

    SELECT '$ ' + CAST(CAST(Round(SUM(Current_Year),0) as INT) -

    CAST(Round(SUM(Previous_Year),0) as INT) as varchar)

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

Viewing 3 posts - 1 through 2 (of 2 total)

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