Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Operand data type varchar is invalid for subtract operator Expand / Collapse
Author
Message
Posted Monday, May 5, 2014 12:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 4, 2014 6:52 AM
Points: 49, Visits: 155
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))
Post #1567418
Posted Monday, May 5, 2014 1:20 AM This worked for the OP Answer marked as solution


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:03 AM
Points: 5,019, Visits: 10,559
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1567422
Posted Monday, May 5, 2014 1:20 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:34 PM
Points: 1,920, Visits: 19,335
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
Post #1567424
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse