时间戳

首页 > 解决方案 > 按日期过滤记录

sql-server - 按日期过滤记录

问题描述

我想过滤两个日期之间的数据，但是将其存储在临时变量中并执行时，出现以下错误

消息 8114，级别 16，状态 5，第 11
行将数据类型 varchar 转换为日期时间时出错

此代码有效：

DECLARE @Where VARCHAR(400)
DECLARE @FromDate DATETIME
DECLARE @ToDate DATETIME

SET @FromDate = '2018-11-02'
SET @ToDate = '2018-11-19' 

SELECT * 
FROM StudentInfo studentinfo 
WHERE CreatedDate BETWEEN  @FromDate AND @ToDate  

CREATE PROCEDURE GetUserData --'2018-11-02','2018-11-17'
    @FromDate DATETIME,
    @ToDate  DATETIME
AS
BEGIN
    DECLARE @ab VARCHAR(400)
    SET @ab= 'select StudID,FirstName,LastName,CountryCode+(SELECT REPLACE(LTRIM(REPLACE(Contact,''0'','' '')),'' '',''0'')) as Contact,EmailID1,CurrentEdu,PreferredCountry,CurrentInstitution,

CONVERT(VARCHAR(10),CreatedDate,103) as RegistrationDate,
OrganisationID,EventId,CreatedDate

  from studentinfo
where 1=1 and
     CreatedDate BETWEEN'+ @FromDate+'and'+ @ToDate+''

     EXEC @ab
END

标签： sql-server

解决方案

declare @ab varchar(400)
  set @ab= 'select StudID,FirstName,LastName,CountryCode+(SELECT REPLACE(LTRIM(REPLACE(Contact,''0'','' '')),'' '',''0'')) as Contact,EmailID1,CurrentEdu,PreferredCountry,CurrentInstitution,

CONVERT(VARCHAR(10),CreatedDate,103) as RegistrationDate,
OrganisationID,EventId,CreatedDate

  from studentinfo
where 1=1 and
     CreatedDate BETWEEN BETWEEN '''+convert(varchar, @FromDate , 20)+''' and '''+ convert(varchar,  @ToDate, 20)+''''

     exec (@ab)
END

推荐阅读