CODEWITHSUNDEEP
sqlt-sql
mjmoody383
mjmoody383

Reputation: 358

Datetime conversion / mixed variables in dynamic SQL

I have a table that has multiple DateTime columns -- TblDate1, TblDate2, TblDate3, etc...

I require a query that accepts a parameter to specify which date column to compare with another date.

Here is my query attempt -

DECLARE @DateColumn nvarchar(128) SET @DateColumn = 'TblDate2'
DECLARE @DateStart datetime SET @DateStart = DATEADD(d, -7, getdate())

EXECUTE sp_executesql
N'SELECT * FROM MessagesTable WHERE @DateStartIN < @DateColumnIN'
, N'@DateStartIN datetime, @DateColumnIN nvarchar(128)'
, @DateStartIN = @DateStart
, @DateColumnIN = @DateColumn

Returns error -

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

Upvotes: 0

Views: 625

Answers (1)

jlee88my
jlee88my

Reputation: 3043

Try this:

DECLARE @DateColumn VARCHAR(128)    SET @DateColumn = 'TblDate2'
DECLARE @DateStart  DATETIME    SET @DateStart  = DATEADD(d, -7, GETDATE())

DECLARE @pvsSQL  NVARCHAR(256)
SET @pvsSQL = 'SELECT * FROM MessagesTable WHERE '+ @DateColumn +' < ''' + CONVERT(VARCHAR, @DateStart ) + ''';'

EXECUTE sp_executesql @pvsSQL

Upvotes: 2

Related Questions