Conversion failed when converting date and/or time from character string while run the stored procedure

Question

@runTime = N'{"runTimes":[{"startTime":"20/Jan/2022 12:44:08","endTime":"20/Jan/2022 12:48:20"}]}',

'select [DateTime], [TagName] as names, [Value] as tablevalue FROM [Runtime].[dbo].[History]
where TagName in (' + @Parameters + ')
AND ([DateTime] BETWEEN ''' + (SELECT DATEADD(minute, DATEPART(TZoffset, SYSDATETIMEOFFSET()), startTime) FROM OpenJson(@runTime,CONCAT('$.runTimes[','',@i,']')) WITH (
startTime NVARCHAR(MAX)) AS Runs) + ''' AND '''+ (SELECT DATEADD(minute, DATEPART(TZoffset, SYSDATETIMEOFFSET()), endTime) FROM OpenJson(@runTime,CONCAT('$.runTimes[','',@i,']'))WITH (
endTime NVARCHAR(MAX)) AS Runs) +''') AND wwRetrievalMode =''Average''
AND wwResolution = ' + CAST(@Resolution as NVARCHAR(max)) + '
AND wwQualityRule = ''Extended''
AND wwVersion = ''Latest'' ';

Could you please help me to resolve this issue

Answer 1

Hi @dinesh kumar ,

{"startTime":"20/Jan/2022 12:44:08","endTime":"20/Jan/2022 12:48:20"}]}',

Maybe the date and time style is not right, at lease it is not correct to put Jan and numbers together

Conversion failed when converting date and/or time from character string while inserting datetime

How to Fix ‘Conversion Failed when Converting Date and/or Time from Character String’ Error?

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

As a start, stop using dynamic SQL, so that the code is easier to read and maintain. There are a number of better ways to handle that @Parameters thing. See here for ideas: https://www.sommarskog.se/arrays-in-sql.html.

Most likely your conversion issue is due to bad data, but the code needs to be cleaned up before we can look at it.

Answer 3

Could you please help me to resolve this issue

For this please post table design, some sample data as DML statement and the expected result; without it is not possible to assist you.
E.g. what's the data content of variable @ Parameters and @ Resolution; we can't guess that?