sql - What is the error The data types varchar and datetime2 are incompatible in the add operator -

declare @state varchar(32) =null,         @industry varchar(128)= null,         @listsource varchar(128) = null,         @timezone varchar(30) =null declare @today datetime set     @today = getdate()  declare @ssql nvarchar(3000) set @state=1 set @industry=1 set @listsource=1 set @timezone=5 set @ssql =  'select top 20 p.id'+  char(10) +'from dbo.prospects p (nolock)'+ char(10) + 'where p.state=isnull('+ char(39)+@state +char(39)+','+'p.state)'+ char(10) + 'and p.industry ='+@industry + char(10) +'and p.listsource='+@listsource+ char(10) +' , p.statusid not in(-1,2,4,5,6,7,8,9,12,13,14)'+ char(10) +' , isnull(p.pushdate,'+char(39)+'1/1/1900'+char(39)+')<='+char(39)+@today+char(39)+ char(10) +'and p.timezone ='+@timezone+ char(10) +' order isnull(p.lastactivitydate,'+char(39)+'1/1/1900'+char(39)+')' 

this immediate problem:

')<='+char(39)+@today+char(39) 

you're trying use datetime in string concatenation. date isn't text - it's date. if want use in string concatenation, need convert text first, e.g.

')<=' + char(39) + convert(varchar(23), @today, 126) + char(39) 

(see cast , convert documentation available styles. style 126 takes 23 characters, reckoning.)

the next problem building sql horribly insecure - you're vulnerable sql injection attacks if client did right thing , called using parameters. haven't shown you're doing ssql, should try hard can not building dynamic sql. there's certainly better solution available. avoiding dynamic sql, you'll end with:

• much more readable code (every char(39) go, along string concatenation)
• security against sql injection attacks
• fewer conversions between non-text values , text, reduces opportunity error