0

ERROR :Msg 8117, Level 16, State 1, Line 37 Operand data type varchar is invalid for minus operator.

Ricky TusJan 23, 2020 | edited Jan 23, 2020 - by @sqldibia

What's the right way to cast the query below to escape this datatype error:

Msg 8117, Level 16, State 1, Line 37 Operand data type varchar is invalid for minus operator.

I've tried casting (DAY,-@RefreshDays,GETDATE()) as date. Not exactly sure which data type to cast it to as its supposed to be a date.

DECLARE @RefreshDays VARCHAR(20)='2020-10-01' 
select FORMAT(DATEADD(DAY,-@RefreshDays,GETDATE()),'yyyyMMdd') 
select @RefreshDays
Emeka Okeke Author Jan 23, 2020
0

The second argument of the DATEADD system function must be a numeric integer type. This means it must be a whole number like 1,2,3,4... etc and not a string in your case.

The problem with your query is that you passed string-typed variable @RefreshDays to the DATEADD function.

The error message:

ERROR :Msg 8117, Level 16, State 1, Line 37 Operand data type varchar is invalid for minus operator.

simply means that you tried to use the minus sign for a string variable -@RefreshDays instead of a numeric variable which is what the DATEADD function expected at that argument position - that is the second argument/parameter

0
Ricky Tus+ Follow
joinedDec 28, 2019

More from @sqljunkyking