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: Today @ 3:26 AM
Points: 48, Visits: 147
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: Yesterday @ 9:14 AM
Points: 5,018, Visits: 10,527
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 @ 2:52 PM
Points: 1,899, Visits: 18,901
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 !
__________________________________________________________________
Post #1567424
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse