Operand data type varchar is invalid for subtract operator

• vigneshkumart50

  Ten Centuries

  Points: 1205

  More actions

  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))

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  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

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  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