CODEWITHSUNDEEP
sql-servert-sql
ukulele
ukulele

Reputation: 87

Problem passing a datetime variable into a dynamic SQL query

I'm trying to execute a dynamic SQL query, but I'm getting an error Msg 105, Level 15, State 1, Line 1 Unclosed quotation mark after the character string '2020-09-15 18:'. Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '2020-09-15 18:'

There must be something wrong with the datetime variable. Can anybody suggest how this should be done properly ?

declare @paramDailyOutput nvarchar(100), @retrieveDailyVal real
declare @paramDaily float
declare @retrieveDailyID varchar(100) = 'table_53'

declare @startTime datetime, @currTime datetime = GETDATE()
set @startTime = DATEADD(hour, -1, @currTime)

             set @paramDailyOutput = 'SELECT @paramDaily = max(value) - min(value) FROM ' + @retrieveDailyID + ' where read_date between ''' + convert(nvarchar(200), @startTime, 120) + ''' and ''' + convert(nvarchar(200), @currTime, 120) + ''''
             exec sp_executesql @paramDailyOutput, N' @paramDaily float OUTPUT', @paramDaily = @paramDaily output
             select @paramDaily

Upvotes: 0

Views: 173

Answers (1)

RBarryYoung
RBarryYoung

Reputation: 56725

You strings are too short, you're going way over 100 characters.

Try changing everything to 250 or even 300 characters. Or better yet, NVARCHAR(1000).

Upvotes: 1

Related Questions