从charachter字符串错误转换日期时间时转换faild [英] conversion faild when converting date time from charachter string error

问题描述

大家好，当我运行此存储过程时，它会给我消息错误



从charachter sting转换日期时间转换faild



存储过程如下

 创建  proc  searchData 
 
  @ StartDate   datetime ，
  @ EndDate   datetime  
 
 < span class =code-keyword> as  
 声明  @ SQLQuery  < span class =code-keyword> 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  * < span class =code-keyword>来自 dbo.Employee e 其中 JoinDate   < span class =code-sdkkeyword> @ 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 （< span class =code-digit> 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 sting

stored procedure as following

Create proc searchData

    @StartDate datetime, 
    @EndDate datetime

as
Declare @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, 

as

select * from dbo.Employee e where JoinDate between @StartDate and @EndDate

Now what is the proplem in first stored procedure
Please 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 DATETIME
AS
BEGIN
	--@DemoDate is just place holder if JoinDate is null in table
	DECLARE @DemoDate DATETIME
	SET @DemoDate = GETDATE()

	SELECT * FROM Employee
	WHERE COALESCE(JoinDate, @DemoDate) 
	BETWEEN COALESCE(@StartDate, JoinDate, @DemoDate)
	AND COALESCE(@EndDate, JoinDate, @DemoDate)
END

Whether 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 DATETIME
AS
BEGIN
	SELECT * FROM Employee
	WHERE JoinDate
	BETWEEN COALESCE(@StartDate, JoinDate)
	AND COALESCE(@EndDate, JoinDate)
END

Any suggestions are acceptable.

Change your first procedure in the following format

Create proc searchData

    @StartDate datetime,
    @EndDate datetime

as
Declare @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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！