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 WITH (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) +'
AND p.StatusID not in(-1,2,4,5,6,7,8,9,12,13,14)'+ CHAR(10) +'
AND isnull(p.PushDate,'+CHAR(39)+'1/1/1900'+CHAR(39)+')<='+CHAR(39)+@Today+CHAR(39)+
CHAR(10) +'AND p.TimeZone ='+@TimeZone+ CHAR(10) +'
order by isnull(p.LastActivityDate,'+CHAR(39)+'1/1/1900'+CHAR(39)+')'
Jon Skeet
people
quotationmark

This is the immediate problem:

')<='+CHAR(39)+@Today+CHAR(39)

You're trying to use a DATETIME in string concatenation. A date isn't text - it's a date. So if you want to use it in string concatenation, you need to convert it to text first, e.g.

')<=' + CHAR(39) + CONVERT(VARCHAR(23), @Today, 126) + CHAR(39)

(See CAST and CONVERT documentation for the available styles. Style 126 takes 23 characters, by my reckoning.)

The next problem is that building up SQL like this is horribly insecure - you're vulnerable to SQL injection attacks even if the client did the right thing and called you using parameters. You haven't shown us what you're doing with sSQL, but you should try as hard as you can not to building up dynamic SQL. There's almost certainly a better solution available. By avoiding dynamic SQL, you'll end up with:

  • Much more readable code (every CHAR(39) would go, along with the string concatenation)
  • Security against SQL injection attacks
  • Fewer conversions between non-text values and text, which reduces the opportunity for error

people

See more on this question at Stackoverflow