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
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 7:18 AM
Points: 55, Visits: 184
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


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:58 PM
Points: 4,655, Visits: 11,126
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

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #1567422
Posted Monday, May 5, 2014 1:20 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:42 AM
Points: 1,943, Visits: 20,180
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