Problem passing a datetime variable into a dynamic SQL query

Question

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

To write dynamic sql you must be able to debug it when something goes wrong. So rather than rage-quit the development, just select the string you generated so you can see exactly what the code you wrote did. — SMor
– SMor, Commented Sep 15, 2020 at 17:32

RBarryYoung · Accepted Answer · 2020-09-15 17:25:23Z

1

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).

answered Sep 15, 2020 at 17:25

RBarryYoung

57k15 gold badges99 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Problem passing a datetime variable into a dynamic SQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related