从charachter字符串错误转换日期时间时转换faild

本文介绍了从charachter字符串错误转换日期时间时转换faild的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 大家好，当我运行此存储过程时，它会给我消息错误 从charachter sting转换日期时间转换faild 存储过程如下 创建 proc searchData @ StartDate datetime ， @ EndDate datetime as 声明 @ SQLQuery as nvarchar （ 2000 ） SET @ SQLQuery = ' SELECT * from Employee Where（1 = 1）' 如果（ @ StartDate 不 NULL ） AND （ @ EndDate 不 NULL ） 设置 @ SQLQuery = @ SQLQuery + ' 和（JoinDate BETWEEN' + @ StartDate + ' AND' + @EndDate + ' ）' Exec （ @ SQLQuery ） JoinDat在表Employee中找到e作为日期时间 但是当我将存储过程设为如下 它在formate dd / mm / yyyy这就是我需要的东西 ALTER proc [dbo]。[searchData] @ StartDate datetime @ EndDate datetime ， as select * 来自 dbo.Employee e 其中 JoinDate @ StartDate 和 @ EndDate 现在第一个存储过程中的问题是什么？请帮忙我尽可能解决方案 使用 sp_executesql [ ^ ]而不是 EXEC ： 声明 @ SQLQuery as nvarchar （ 2000 ）; SET @ SQLQuery = N ' SELECT * from Employee Where（1 = 1）'; 如果（ @ StartDate 是 不 NULL ） AND （ @ EndDate 不 NULL ） SET @ SQLQuery = @ SQLQuery + N ' 和（JoinDate @StartDate And @EndDate）'; EXEC sp_executesql @ SQLQuery ， N @ StartDate datetime，@ EndDate datetime'， @ StartDate ， @ EndDate ; 在您的程序中进行以下更改，它一定会有效对于你。 创建 程序 searchData @ StartDate DATETIME ， @EndDate DATETIME AS BEGIN - 如果JoinDate为null，@ DemoDate只是占位符表 DECLARE @ DemoDate DATETIME SET @ DemoDate = GETDATE（） SELECT * FROM 员工 WHERE COALESCE （JoinDate， @ DemoDate ） BETWEEN COALESCE （ @ StartDate ，JoinDate， @ DemoDate ） AND COALESCE （ @ EndDate ，JoinDate， @ DemoDate ） END 参数@StartDate，@ EndDate是否具有空值或日期值y我们的程序将正常工作。如果您确定表中的JoinDate始终不为null，那么以下更改就足够了。 创建 PROCEDURE searchData @ StartDate DATETIME ， @ EndDate DATETIME AS BEGIN SELECT * FROM 员工 WHERE JoinDate BETWEEN COALESCE （ @ StartDate ，JoinDate） AND COALESCE （ @ EndDate ，JoinDate） END 任何建议都可以接受。 按以下格式更改您的第一个程序 创建 proc searchData @ StartDate datetime ， @ EndDate datetime as 声明 @ SQLQuery as nvarchar （ 2000 ） SET @ SQLQuery = ' SELECT * from Employee Where（1 = 1）' 如果（ @ StartDate 不 NULL ） AND （ @ EndDate 不 NULL ） 设置 @ SQLQuery = @ SQLQuery + ' 和（JoinDate BETWEEN''' + convert （ varchar ， @ StartDate ， 103 ）+ ' ''AND''' + convert（ varchar ， @EndDate ， 103 ）+ ' ''）' Exec （ @ SQLQuery ） Hi guy when i run this Stored procedure it give me message error conversion faild when converting date time from charachter stingstored procedure as following Create proc searchData @StartDate datetime, @EndDate datetimeasDeclare @SQLQuery as nvarchar(2000)SET @SQLQuery ='SELECT * from Employee Where (1=1)' If (@StartDate is not NULL) AND (@EndDate is not NULL) Set @SQLQuery = @SQLQuery + ' And (JoinDate BETWEEN '+ @StartDate +' AND '+@EndDate+')'Exec (@SQLQuery)JoinDate found in table Employee as datetime but when i make stored procedure as following it work in formate dd/mm/yyyy and this is what i need ALTER proc [dbo].[searchData]@StartDate datetime @EndDate datetime, asselect * from dbo.Employee e where JoinDate between @StartDate and @EndDateNow what is the proplem in first stored procedurePlease help me if possible 解决方案 Use sp_executesql[^] instead of EXEC:Declare @SQLQuery as nvarchar(2000);SET @SQLQuery = N'SELECT * from Employee Where (1=1)';If (@StartDate is not NULL) AND (@EndDate is not NULL) SET @SQLQuery = @SQLQuery + N' And (JoinDate Between @StartDate And @EndDate)';EXEC sp_executesql @SQLQuery, N'@StartDate datetime, @EndDate datetime', @StartDate, @EndDate;Make following changes in your procedure it will definitely work for you.CREATE PROCEDURE searchData@StartDate DATETIME,@EndDate DATETIMEASBEGIN--@DemoDate is just place holder if JoinDate is null in tableDECLARE @DemoDate DATETIMESET @DemoDate = GETDATE()SELECT * FROM EmployeeWHERE COALESCE(JoinDate, @DemoDate) BETWEEN COALESCE(@StartDate, JoinDate, @DemoDate)AND COALESCE(@EndDate, JoinDate, @DemoDate)ENDWhether parameters @StartDate, @EndDate having null values or date values your procedure will work fine. If you are dam sure that JoinDate in table is always not null then following changes are sufficient.CREATE PROCEDURE searchData@StartDate DATETIME,@EndDate DATETIMEASBEGINSELECT * FROM EmployeeWHERE JoinDateBETWEEN COALESCE(@StartDate, JoinDate)AND COALESCE(@EndDate, JoinDate)ENDAny suggestions are acceptable.Change your first procedure in the following formatCreate proc searchData @StartDate datetime, @EndDate datetimeasDeclare @SQLQuery as nvarchar(2000)SET @SQLQuery ='SELECT * from Employee Where (1=1)' If (@StartDate is not NULL) AND (@EndDate is not NULL) Set @SQLQuery = @SQLQuery + ' And (JoinDate BETWEEN '''+ convert(varchar, @StartDate, 103) +''' AND '''+convert(varchar, @EndDate, 103)+''')'Exec (@SQLQuery) 这篇关于从charachter字符串错误转换日期时间时转换faild的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！