SELECT MineID,StafferID,JHBanID,BanTypeID,PlanDate,PlanStartTime,PlanEndTime,
PlanChangeReason
FROM(
SELECT Mineid,StafferID,ISNULL(BGBanID,JHBanID) JHBanID,BanTypeID,
ISNULL(PlanDate1,PlanDate) PlanDate,
ISNULL(PlanStartTime1,PlanStartTime) PlanStartTime ,
ISNULL(PlanEndTime1,PlanEndTime) PlanEndTime,PlanChangeReason
FROM DownPlan
WHERE (CONVERT(VARCHAR(100),PlanDate,23)>=CONVERT(VARCHAR(100),@QSSJ,23) and CONVERT(VARCHAR(100),PlanDate,23)<=CONVERT(VARCHAR(100),@QSSJ,23) ) OR
(CONVERT(VARCHAR(100),PlanDate1,23)>=CONVERT(VARCHA(100),@QSSJ,23) and
CONVERT(VARCHAR(100),PlanDate1,23)<=CONVERT(VARCHAR(100),@QSSJ,23))
) d
WHERE CONVERT(VARCHAR(100),d.PlanDate,23)>=CONVERT(VARCHAR(100),@QSSJ,23) and
CONVERT(VARCHAR(100),d.PlanDate,23)<=CONVERT(VARCHAR(100),@QSSJ,23)
@QSSJ varchar(19)@QSSJ 的格式 2011-07-18
测试过2011-07-18 00:00:00也不行
PlanChangeReason
FROM(
SELECT Mineid,StafferID,ISNULL(BGBanID,JHBanID) JHBanID,BanTypeID,
ISNULL(PlanDate1,PlanDate) PlanDate,
ISNULL(PlanStartTime1,PlanStartTime) PlanStartTime ,
ISNULL(PlanEndTime1,PlanEndTime) PlanEndTime,PlanChangeReason
FROM DownPlan
WHERE (CONVERT(VARCHAR(100),PlanDate,23)>=CONVERT(VARCHAR(100),@QSSJ,23) and CONVERT(VARCHAR(100),PlanDate,23)<=CONVERT(VARCHAR(100),@QSSJ,23) ) OR
(CONVERT(VARCHAR(100),PlanDate1,23)>=CONVERT(VARCHA(100),@QSSJ,23) and
CONVERT(VARCHAR(100),PlanDate1,23)<=CONVERT(VARCHAR(100),@QSSJ,23))
) d
WHERE CONVERT(VARCHAR(100),d.PlanDate,23)>=CONVERT(VARCHAR(100),@QSSJ,23) and
CONVERT(VARCHAR(100),d.PlanDate,23)<=CONVERT(VARCHAR(100),@QSSJ,23)
@QSSJ varchar(19)@QSSJ 的格式 2011-07-18
测试过2011-07-18 00:00:00也不行
可查看版本号
FROM(
SELECT Mineid,StafferID,ISNULL(BGBanID,JHBanID) JHBanID,BanTypeID,
ISNULL(PlanDate1,PlanDate) PlanDate,ISNULL(PlanStartTime1,PlanStartTime) PlanStartTime ,
ISNULL(PlanEndTime1,PlanEndTime) PlanEndTime,PlanChangeReason
FROM DownPlan WHERE (PlanDate >= @QSSJ and PlanDate<=@QSSJ) OR
(PlanDate1>=@QSSJ and PlanDate1<=@QSSJ)
) d
WHERE PlanDate >= @QSSJ and PlanDate<=@QSSJ这样写就有结果
应该就是 PlanDate = @QSSJ