表结构:
CREATE TABLE coursePlan
(
[id] INT IDENTITY(1,1) PRIMARY KEY,
tid INT FOREIGN KEY REFERENCES teacher(tid),
cid INT FOREIGN KEY REFERENCES course(cid),
beginDate DATETIME NOT NULL,
endDate DATETIME NOT NULL,
note VARCHAR(200) NULL
)
INSERT INTO coursePlan VALUES(2,1,'2006-10-24 8:40:00','2006-10-24 10:10:00','')
数据库就这么1条记录
SELECT *
FROM coursePlan
WHERE tid = 2
AND EXISTS
(SELECT *
FROM coursePlan
WHERE '2006-10-24 8:10:00' > endDate
OR
结果既然能显示这条记录,好象比较的时候只比较到2006-10-24,后面的8:10:00被忽略了啊。
不想用分割字符串处理的方法,麻烦!有没简单的方法解决?谢谢!
CREATE TABLE coursePlan
(
[id] INT IDENTITY(1,1) PRIMARY KEY,
tid INT FOREIGN KEY REFERENCES teacher(tid),
cid INT FOREIGN KEY REFERENCES course(cid),
beginDate DATETIME NOT NULL,
endDate DATETIME NOT NULL,
note VARCHAR(200) NULL
)
INSERT INTO coursePlan VALUES(2,1,'2006-10-24 8:40:00','2006-10-24 10:10:00','')
数据库就这么1条记录
SELECT *
FROM coursePlan
WHERE tid = 2
AND EXISTS
(SELECT *
FROM coursePlan
WHERE '2006-10-24 8:10:00' > endDate
OR
结果既然能显示这条记录,好象比较的时候只比较到2006-10-24,后面的8:10:00被忽略了啊。
不想用分割字符串处理的方法,麻烦!有没简单的方法解决?谢谢!
FROM coursePlan
WHERE tid = 2
AND EXISTS
(SELECT *
FROM coursePlan
WHERE '2006-10-24 8:10:00.000' > endDate
OR '2006-10-28 07:30:00' < beginDate)
上面的OR后面补上!
FROM coursePlan a
WHERE tid = 2
AND EXISTS
(SELECT *
FROM coursePlan
WHERE convert(datetime,'2006-10-24 8:10:00') > endDate
)
FROM coursePlan
WHERE tid = 2
AND EXISTS
(SELECT *
FROM coursePlan
WHERE '2006-10-24 8:10:00.000' > endDate
OR '2006-10-28 07:30:00' < beginDate)
结果没有记录,没问题啊
这个条件,LZ的那条记录本来就不满足
FROM coursePlan a
WHERE tid = 2
AND EXISTS
(SELECT *
FROM coursePlan
WHERE convert(datetime,'2006-10-24 8:10:00') > endDate
)
endDate < '2006-10-24 8:10:00.000' --enddate在它之前,LZ的那条不满足
or begindate > '2006-10-28 07:30:00' --这里的10-28是不是看成10-24了
--begindate在它之后,LZ的那条也不满足
都不满足当然就没有记录了
这是我的存储过程,已经通过!
ALTER PROC AddCourseInfo --2,2,'2006-10-25 9:40:00','2006-10-26 10:35:00',''/*测试能否插入*/
(
@tid INT,
@cid INT,
@beginDate DATETIME,
@endDate DATETIME,
@note VARCHAR(200)
)
AS
IF(@endDate > @beginDate)
BEGIN
IF NOT EXISTS(SELECT *
FROM
(SELECT *
FROM coursePlan
WHERE tid = @tid) AS coursePlanByTid
WHERE EXISTS
(SELECT *
FROM coursePlan
WHERE (@beginDate >= coursePlanByTid.beginDate
AND @beginDate <= coursePlanByTid.endDate)
OR (@endDate >= coursePlanByTid.beginDate
AND @endDate <= coursePlanByTid.endDate)
OR (@beginDate <= coursePlanByTid.beginDate
AND @endDate >= coursePlanByTid.endDate)))
BEGIN
INSERT INTO coursePlan VALUES /*日期不存在重叠则登记该老师授课计划*/
(
@tid,
@cid,
@beginDate,
@endDate,
@note
)
END
ELSE
RETURN 1 --返回的错误信息
END
ELSE
RETURN 1
RETURN @@ERROR --返回的错误信息当初以为先要把SELECT *
FROM
(SELECT *
FROM coursePlan
WHERE tid = @tid做成一个查询结果再去查询,试了下OK,通过!
可能是查询计划的原因,把内存的数据直接给我了,不去查询数据库了!
返回空啊