问题描述
你好，
我的查询得到错误如何解决：
我有尝试单独运行动态查询但是： -
Hello,
my query its getting error how it can be resolve:
I have tried run dynamic query separately but:-
DECLARE @ContractDurationColumnName NVARCHAR(255)
Declare @SQL NVARCHAR(2000)
set @ContractDurationColumnName = (select ColumnName from LCompanySpecificColumns as CSC where CSC.Label='Duration' and CSC.CompanyCode = 'DE' and CSC.TableName = 'LProducts' and CSC.SelecterType = 'Kias-Soc')
SET @SQL='select LP.ProductCode as ProductId,LP.Name as PRODUCT_DESCRIPTION,PC.Name as PRODUCT_ID_CATEGORY,PS.Name as SOURCE_SYSTEM,' + @ContractDurationColumnName + ' as ESTIMATED_CONTRACT_DURATION_ID from LProducts as LP
inner join RSysCat as SC on LP.SysCatId=SC.Id
inner join RProductCategories PC on SC.CategoryId=PC.Id
inner join RProductSystems PS on SC.SystemId=PS.Id
where LP.CreatedDateTime>= ' +CONVERT(datetime,'2017-11-07 10:33:57.000') + ' and LP.UpdatedDateTime <= ' + CONVERT(datetime,'2018-01-04 10:54:20.510')
--select @SQL
exec sp_executesql @SQL
但它也会出错： -
but it also getting error:-
Conversion failed when converting date and/or time from character string. in sql server
请提前帮助我谢谢
Ankit Agarwal
软件工程师
我尝试过：
我的实际成绩： -
Please help me thanks in advance
Ankit Agarwal
Software Engineer
What I have tried:
Its my actual prodedure:-
Create proc [dbo].[SpS15ProductMasterData]
@StartDate datetime,
@EndDate datetime
as
begin
DECLARE @ContractDurationColumnName NVARCHAR(255)
Declare @SQL NVARCHAR(2000)
set @ContractDurationColumnName = (select ColumnName from LCompanySpecificColumns as CSC where CSC.Label='Duration' and CSC.CompanyCode = 'DE' and CSC.TableName = 'LProducts' and CSC.SelecterType = 'Kias-Soc')
SET @SQL='select LP.ProductCode as ProductId,LP.Name as PRODUCT_DESCRIPTION,PC.Name as PRODUCT_ID_CATEGORY,PS.Name as SOURCE_SYSTEM,' + @ContractDurationColumnName + ' as ESTIMATED_CONTRACT_DURATION_ID from LProducts as LP
inner join RSysCat as SC on LP.SysCatId=SC.Id
inner join RProductCategories PC on SC.CategoryId=PC.Id
inner join RProductSystems PS on SC.SystemId=PS.Id
where LP.CreatedDateTime>= ' + @StartDate + ' and LP.UpdatedDateTime <= ' + @EndDate
exec sp_executesql @SQL
end
Exec SpS15ProductMasterData '2017-11-07 10:33:57.000','2018-01-04 10:54:20.510'
我的代码获取错误
My code its getting error
推荐答案
...ime>= ' +CONVERT(datetime,'2017-11-07 10:33:57.000') + ' and ...会立即将其转换回字符串，以便将其包含在您的命令中。
你需要这样做，因为你是动态决定返回哪一列。
但是直接将字符串放在那里：
Which immediately converts it back to a string so it can be included in your command.
And you need to do that because you are dynamically deciding which column to return.
But that puts the string in there directly:
...ime>= 2017-11-07 10:33:57.000 and ...您需要将datetime转换为字符串并引用它：
You need to convert the datetime to string and quote it:
...where LP.CreatedDateTime>= ''' + CONVERT(NVARCHAR, @StartDate, 126) + ''' and LP.UpdatedDateTime <= ''' + CONVERT(NVARCHAR, @EndDate, 126) + ''''...
SET @SQL='select LP.ProductCode as ProductId, LP.Name as PRODUCT_DESCRIPTION, PC.Name as PRODUCT_ID_CATEGORY, PS.Name as SOURCE_SYSTEM, ' + @ContractDurationColumnName + ' as ESTIMATED_CONTRACT_DURATION_ID from LProducts as LP
inner join RSysCat as SC on LP.SysCatId = SC.Id
inner join RProductCategories PC on SC.CategoryId = PC.Id
inner join RProductSystems PS on SC.SystemId = PS.Id
where LP.CreatedDateTime >= @StartDate and LP.UpdatedDateTime <= @EndDate';
exec sp_executesql @SQL,
N'@StartDate datetime, @EndDate datetime',
@StartDate = @StartDate,
@EndDate = @EndDate;
[]
这篇关于从字符串转换日期和/或时间时转换失败。在SQL服务器中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！