这是一个SQL子查询,给出的错误为
“从字符串转换日期和/或时间时转换失败。”
我尝试用datetime2替换datetime,但是它不起作用。
declare @cols as NVARCHAR(max)
, @cols2 as NVARCHAR(max)
, @query as NVARCHAR(max) select
@cols = STUFF(
(
select
',' + QUOTENAME(ProductOptionName)
from
OrderProductVariantOption [NL]
inner join OrderProductVariant [NL] on
OrderProductVariant.Id = OrderProductVariantOption.OrderProductVariantId
where
(
OrderProductVariant.AcceptedById = '[Business|0]'
or OrderProductVariant.MRefId = '[Business|0]'
)
and [OrderProductVariant.MarketplaceGroupId=Marketplace]
and OrderProductVariant.DateCreated >= cast(cast('[startdate]' as date) as datetime2)
and OrderProductVariant.DateCreated < cast(cast('[enddate]' as date) as datetime2)
group by
ProductOptionName
order by
ProductOptionName for XML path('')
, TYPE
)
. value('.', 'NVARCHAR(MAX)')
, 1
, 1
, ''
)
删除[NL],因为[NL]已多次使用。
也许OrderProductVariant.DateCreated日期格式不一样。因此也请纠正其格式。
并强制转换(OrderProductVariant.DateCreated作为日期)> =强制转换([startdate]作为日期)
最后,您的代码将如下所示:
declare @cols as NVARCHAR(max)
, @cols2 as NVARCHAR(max)
, @query as NVARCHAR(max) select
@cols = STUFF(
(
select
',' + QUOTENAME(ProductOptionName)
from
OrderProductVariantOption
inner join OrderProductVariant on
OrderProductVariant.Id = OrderProductVariantOption.OrderProductVariantId
where
(
OrderProductVariant.AcceptedById = '[Business|0]'
or OrderProductVariant.MRefId = '[Business|0]'
)
and [OrderProductVariant.MarketplaceGroupId=Marketplace]
and cast(OrderProductVariant.DateCreated as date) >= cast([startdate] as date)
and cast(OrderProductVariant.DateCreated as date) < cast([enddate] as date)
group by
ProductOptionName
order by
ProductOptionName for XML path('')
, TYPE
)
. value('.', 'NVARCHAR(MAX)')
, 1
, 1
, ''
)
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句