从字符串转换日期和/或时间时转换失败。在SQL服务器中 [英] Conversion failed when converting date and/or time from character string. In SQL server

问题描述

你好，



我的查询得到错误如何解决：



我有尝试单独运行动态查询但是： -

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

推荐答案

问题是您要将字符串转换为DateTime：

The problem is that you are converting a string to a DateTime:

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

避免将值连接到查询中;改为使用参数：

Avoid concatenating values into queries; use parameters instead:

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;

sp_executesql（Transact-SQL）| Microsoft Docs [ ^ ]

这篇关于从字符串转换日期和/或时间时转换失败。在SQL服务器中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！