34,575
社区成员
发帖
与我相关
我的任务
分享
IF (OBJECT_ID('Times') IS NOT NULL)
DROP TABLE Times
CREATE TABLE Times
(
BeginTime DATETIME,
EndTime DATETIME,
)
INSERT Times
SELECT '2010-08-30 05:01:00','2010-08-30 05:30:00' UNION ALL --NO
SELECT '2010-08-30 06:00:00','2010-08-30 07:20:00' UNION ALL --符合
SELECT '2010-08-30 07:30:00','2010-08-30 07:50:00' UNION ALL --符合
SELECT '2010-08-30 07:55:00','2010-08-30 08:30:00' UNION ALL --符合
SELECT '2010-08-30 08:40:00','2010-08-30 09:30:00' union ALL --NO
SELECT '2010-08-30 04:30:00','2010-08-30 09:00:00' union ALL --符合
SELECT '2010-08-30 07:00:00','2010-08-30 08:00:00' --符合
--查询条件 2010-08-30 07:00:00 :2010-08-30 08:00:00 ( starttime,Endtime 等于或与其有交错的:) 以上备注为符合的的表示最终的有效记录
-- 以下查询语句该如何写 ,可以 使用 函数
SELECT * FROM Times t WHERE ………………
--存储过程
Create Proc p_Times
As
Declare @starttime DateTime,@Endtime DateTime
Select @starttime='2010-08-30 07:00:00'
Select @Endtime='2010-08-30 08:00:00'
Select * From Times
Where @Endtime>=BeginTime And @starttime<=EndTime
Go
--调用存储过程
Exec p_Times
Go
declare @starttime datetime,@Endtime datetime
set @starttime='2010-08-30 07:00:00'
set @Endtime='2010-08-30 08:00:00'
SELECT * FROM Times
where @Endtime>=BeginTime and @starttime<=EndTime
/**
BeginTime EndTime
----------------------- -----------------------
2010-08-30 06:00:00.000 2010-08-30 07:20:00.000
2010-08-30 07:30:00.000 2010-08-30 07:50:00.000
2010-08-30 07:55:00.000 2010-08-30 08:30:00.000
2010-08-30 04:30:00.000 2010-08-30 09:00:00.000
2010-08-30 07:00:00.000 2010-08-30 08:00:00.000
(5 行受影响)
**/
declare @starttime datetime,@Endtime datetime
set @starttime='2010-08-30 07:00:00'
set @Endtime='2010-08-30 08:00:00'
SELECT * FROM Times t
WHERE @starttime between BeginTime and EndTime
or @Endtime between BeginTime and EndTime
or BeginTime between @starttime and @Endtime
or EndTime between @starttime and @Endtime
BeginTime EndTime
----------------------- -----------------------
2010-08-30 06:00:00.000 2010-08-30 07:20:00.000
2010-08-30 07:30:00.000 2010-08-30 07:50:00.000
2010-08-30 07:55:00.000 2010-08-30 08:30:00.000
2010-08-30 04:30:00.000 2010-08-30 09:00:00.000
2010-08-30 07:00:00.000 2010-08-30 08:00:00.000
(5 行受影响)