Varchar is invalid for subtract operator

I am running some code on a sharepoint DB, the Size field is an Integer field but when I try to do calcs on it I get the follwoing error.

Msg 8117, Level 16, State 1, Line 1
Operand data type varchar is invalid for subtract operator.
Select
(Select Left(((Sum([Size])*0.0009765625)*0.0009765625), 3) as 'Size combined in MB'
	FROM SharePoint_WSS_Content.dbo.AllDocs Where DirName not like '%/masterpage%'
		and DirName not like '%Reporting Templates%'
		and DirName not like '%/_themes%' 
		and DirName not like '%_catalogs%' 
		and DirName not like '%Style Library%'
		and DirName not like '%/forms%' 
		and DirName not like '%_cts%'
		and DirName not like '%FormServerTemplates%'  
		and DirName not like '%IWConvertedForms%'  
		and DirName not like '%/_t%'
		and DirName not like '%/_w%'
		and leafname not like '%.aspx'
		and leafname not like '%.xsl'
		and leafname not like '%.000'
		and Leafname <> 'Thumbs.db'
		and leafname like '%.%'
		and TimeCreated > convert(char(10), getdate(), 1) 
		and Len(DeleteTransactionId) = 0) - 
(Select Left(((Sum([Size])*0.0009765625)*0.0009765625), 3) as 'Size combined in MB'
FROM SharePoint_WSS_Content.dbo.AllDocs Where DirName not like '%/masterpage%'
		and DirName not like '%Reporting Templates%'
		and DirName not like '%/_themes%' 
		and DirName not like '%_catalogs%' 
		and DirName not like '%Style Library%'
		and DirName not like '%/forms%' 
		and DirName not like '%_cts%'
		and DirName not like '%FormServerTemplates%'  
		and DirName not like '%IWConvertedForms%'  
		and DirName not like '%/_t%'
		and DirName not like '%/_w%'
		and leafname not like '%.aspx'
		and leafname not like '%.xsl'
		and leafname not like '%.000'
		and Leafname <> 'Thumbs.db'
		and leafname like '%.%'
		and TimeLastModified > convert(char(10), getdate(), 1) 
		and Len(DeleteTransactionId) <> 0)

Open in new window

kdeutschAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
chapmandewConnect With a Mentor Commented:
it has a period....you'll have to wrap a replace around it like I did the cast

Select
      CAST(
            REPLACE(
            (
       Select
            Left(((Sum([Size]) * 0.0009765625) * 0.0009765625), 3) as 'Size combined in MB'
       FROM
            SharePoint_WSS_Content.dbo.AllDocs
       Where
            DirName not like '%/masterpage%'
            and DirName not like '%Reporting Templates%'
            and DirName not like '%/_themes%'
            and DirName not like '%_catalogs%'
            and DirName not like '%Style Library%'
            and DirName not like '%/forms%'
            and DirName not like '%_cts%'
            and DirName not like '%FormServerTemplates%'
            and DirName not like '%IWConvertedForms%'
            and DirName not like '%/_t%'
            and DirName not like '%/_w%'
            and leafname not like '%.aspx'
            and leafname not like '%.xsl'
            and leafname not like '%.000'
            and Leafname <> 'Thumbs.db'
            and leafname like '%.%'
            and TimeCreated > convert(char(10), getdate(), 1)
            and Len(DeleteTransactionId) = 0
      ), '.', '') AS INT)
      - CAST(
            REPLACE(
            (
         Select
            Left(((Sum([Size]) * 0.0009765625) * 0.0009765625), 3) as 'Size combined in MB'
         FROM
            SharePoint_WSS_Content.dbo.AllDocs
         Where
            DirName not like '%/masterpage%'
            and DirName not like '%Reporting Templates%'
            and DirName not like '%/_themes%'
            and DirName not like '%_catalogs%'
            and DirName not like '%Style Library%'
            and DirName not like '%/forms%'
            and DirName not like '%_cts%'
            and DirName not like '%FormServerTemplates%'
            and DirName not like '%IWConvertedForms%'
            and DirName not like '%/_t%'
            and DirName not like '%/_w%'
            and leafname not like '%.aspx'
            and leafname not like '%.xsl'
            and leafname not like '%.000'
            and Leafname <> 'Thumbs.db'
            and leafname like '%.%'
            and TimeLastModified > convert(char(10), getdate(), 1)
            and Len(DeleteTransactionId) <> 0
        ), '.', '') AS INT)
0
 
chapmandewCommented:
anywhere you use a LEFT() function, wrap a cast around it.

select cast(left() as int)
0
 
kdeutschAuthor Commented:
HI,
I tried this (Select cast(Left()as int(((Sum([Siz
but received these errors, do i have to wrap whole code in this??

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'Sum'.
Msg 102, Level 15, State 1, Line 21
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 21
Incorrect syntax near 'Sum'.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
chapmandewCommented:
it should look like this:

Select
      CAST((
       Select
            Left(((Sum([Size]) * 0.0009765625) * 0.0009765625), 3) as 'Size combined in MB'
       FROM
            SharePoint_WSS_Content.dbo.AllDocs
       Where
            DirName not like '%/masterpage%'
            and DirName not like '%Reporting Templates%'
            and DirName not like '%/_themes%'
            and DirName not like '%_catalogs%'
            and DirName not like '%Style Library%'
            and DirName not like '%/forms%'
            and DirName not like '%_cts%'
            and DirName not like '%FormServerTemplates%'
            and DirName not like '%IWConvertedForms%'
            and DirName not like '%/_t%'
            and DirName not like '%/_w%'
            and leafname not like '%.aspx'
            and leafname not like '%.xsl'
            and leafname not like '%.000'
            and Leafname <> 'Thumbs.db'
            and leafname like '%.%'
            and TimeCreated > convert(char(10), getdate(), 1)
            and Len(DeleteTransactionId) = 0
      ) AS INT)
      - CAST((
         Select
            Left(((Sum([Size]) * 0.0009765625) * 0.0009765625), 3) as 'Size combined in MB'
         FROM
            SharePoint_WSS_Content.dbo.AllDocs
         Where
            DirName not like '%/masterpage%'
            and DirName not like '%Reporting Templates%'
            and DirName not like '%/_themes%'
            and DirName not like '%_catalogs%'
            and DirName not like '%Style Library%'
            and DirName not like '%/forms%'
            and DirName not like '%_cts%'
            and DirName not like '%FormServerTemplates%'
            and DirName not like '%IWConvertedForms%'
            and DirName not like '%/_t%'
            and DirName not like '%/_w%'
            and leafname not like '%.aspx'
            and leafname not like '%.xsl'
            and leafname not like '%.000'
            and Leafname <> 'Thumbs.db'
            and leafname like '%.%'
            and TimeLastModified > convert(char(10), getdate(), 1)
            and Len(DeleteTransactionId) <> 0
        ) AS INT)
0
 
kdeutschAuthor Commented:
Ok, its working somewhat.

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '31.' to data type int.
0
 
kdeutschAuthor Commented:
Thanks for the learning lesson.  Works great
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.