CODEWITHSUNDEEP
sql-server
VARUN NAYAK
VARUN NAYAK

Reputation: 666

Error: Operand data type varchar is invalid for divide operator

I am using a dynamic stored procedure 

if @ReportType = 'Daily Batch'
begin 
   set @SelectionList= 'select ''' + @tag0 + ''' as Tag0 , ''' + @tag1 + ''' as Tag1 , ''' +  @tag2 + ''' as Tag2 , ''' +  @tag3 + ''' as Tag3 , ''' +  @tag4 + ''' as Tag4 , ''' +  @tag5 + ''' as Tag5 
 , ''' +  @tag6 + ''' as Tag6 , ''' +  @tag7 + ''' as Tag7 , ''' +  @tag8 + ''' as Tag8 , ''' +  @tag9 + ''' as Tag9 , ''' +  @tag10 + ''' as Tag10 , ''' +  @tag11 + ''' as Tag11
 , ' +  @value0 + ' as Value0 , ' +  @value1 + ' as Value1, ' +  @value2 + ' as Value2 , ' +  @value3 + ' as Value3 , ' +  @value4 + ' as Value4 , ' +  @value5 + ' as Value5
 , ' +  @value6 + ' as Value6 , ' +  @value7 + ' as Value7 , ' +  @value8 + ' as Value8 , ' +  @value9 + ' as Value9 , ' +  @value10 + ' as Value10 , ' +  @value11 + ' as Value11         
 , RefProductWgt1, RefProductWgt2, RefProductWgt3, RefProductWgt4, RefProductWgt5, RefProductWgt6, RefProductWgt7, RefProductWgt8, 
                     RefProductWgt9, RefProductWgt10, ProductCount, PassCount, RejectCount, UnderWgts, OverWgts, DoubleCount, ReportId, StartDate as GroupColumn
 FROM  BatchMaster    
  WHERE SUBSTRING(StartDate, 0, charindex('/' , StartDate, 0))='''+ @startdate+''') AND (DeviceId = '''+@devid+''')     

 end

 exec (@SelectionList)

Here @SelectionList data type is nvarchar(3000), I just want to extract data from a DateTime column...

But it is showing this error:

Operand data type varchar is invalid for divide operator.

Here '/' is used as character not divide, because in my column DateStoreLike (dd-mm-yy/HH:MM:SS), and I am trying to extract only date part before '/' symbol

Upvotes: 0

Views: 3638

Answers (1)

How 'bout a Fresca
How 'bout a Fresca

Reputation: 2317

The error you are receiving is because you need to have two single quotes on each side of the slash:

...WHERE SUBSTRING(StartDate, 0, charindex(''/'' , StartDate, 0))='''+ @startdate+''') AND (DeviceId = '''+@devid+''')

For instance, the following two queries will return '5' as the result:

SELECT charindex('/' , 'this/string', 0)

DECLARE @sql VARCHAR (55) = 'SELECT charindex(''/'' , ''this/string'', 0)'
EXEC(@sql)

When you want a single quote to appear within a string, you need to use two single quotes. To demonstrate this, you can run the following:

SELECT '' --This will return an empty string

SELECT '''' --This will return a single quotation mark

Upvotes: 1

Related Questions