转换动态偏移量和日期时间时出错(Error when converting dynamic offset and datetime)

转换动态偏移量和日期时间时出错(Error when converting dynamic offset and datetime)

我有一个偏移量和日期时间的动态参数。

转换日期时我遇到问题。

Declare @STR NVARCHAR (MAX) Declare @offset nvarchar = '+05:00' Declare @paramrequest date = '2017-03-30' SET @STR = 'select .... where ' + CONVERT(DATE, SWITCHOFFSET(CONVERT(DATETIMEOFFSET, + 'REQUESTDATETIME'), @offset )) + ' >=' + CAST(@paramrequest AS DATE); EXECUTE (@STR)

当我运行脚本时，它会让我出现这个错误。 如何解决这个错误？

从字符串转换日期和/或时间时转换失败。

I have a dynamic parameter for offset and datetime.

And I have problem when converting the date.

Declare @STR NVARCHAR (MAX) Declare @offset nvarchar = '+05:00' Declare @paramrequest date = '2017-03-30' SET @STR = 'select .... where ' + CONVERT(DATE, SWITCHOFFSET(CONVERT(DATETIMEOFFSET, + 'REQUESTDATETIME'), @offset )) + ' >=' + CAST(@paramrequest AS DATE); EXECUTE (@STR)

When I run the script, it gets me this error. How to get fix this error?

Conversion failed when converting date and/or time from character string.

最满意答案

您需要正确构建您的字符串。 像这样使用CONCAT ：

SET @STR = CONCAT('select .... where ', 'CONVERT(DATE, SWITCHOFFSET(CONVERT(DATETIMEOFFSET,', 'REQUESTDATETIME),''', @offset,''' )) >= ''', CAST(@paramrequest AS DATE), '''')

You need to build your string correctly. use CONCAT like this:

SET @STR = CONCAT('select .... where ', 'CONVERT(DATE, SWITCHOFFSET(CONVERT(DATETIMEOFFSET,', 'REQUESTDATETIME),''', @offset,''' )) >= ''', CAST(@paramrequest AS DATE), '''')