34,588
社区成员
发帖
与我相关
我的任务
分享
SELECT * FROM 表
WHERE ('2009-01-02' BETWEEN SDD AND EDD)
OR ('2009-01-06' BETWEEN SDD AND EDD )
OR (SDD BETWEEN '2009-01-02' AND '2009-01-06')
OR (EDD BETWEEN '2009-01-02' AND '2009-01-06' )
--> (让你望见影子的墙)生成测试数据,时间:2009-02-18
if not object_id('tb') is null
drop table tb
Go
Create table tb([id] int,[sdd] Datetime,[edd] Datetime)
Insert tb
select 1,'2009-01-01','2009-01-31' union all
select 2,'2009-01-03','2009-01-03' union all
select 3,'2009-01-03','2009-01-08' union all
select 4,'2009-01-01','2009-01-03' union all
select 5,'2009-01-06','2009-01-08' union all
select 6,'2009-01-01','2009-01-01'
Go
Select * from tb
declare @start datetime,@end datetime
set @start='2009-01-02'
set @end='2009-01-06'
select id,sdd,edd
from tb
where (sdd<=@start and edd>=@start) or(sdd>=@start and sdd<=@end)
1 2009-01-01 00:00:00.000 2009-01-31 00:00:00.000
2 2009-01-03 00:00:00.000 2009-01-03 00:00:00.000
3 2009-01-03 00:00:00.000 2009-01-08 00:00:00.000
4 2009-01-01 00:00:00.000 2009-01-03 00:00:00.000
5 2009-01-06 00:00:00.000 2009-01-08 00:00:00.000
DECLARE @a TABLE(id INT,sdd varchar(10),edd VARCHAR(10))
insert @a select 1,'2009-01-01','2009-01-31'
union all select 2,'2009-01-03','2009-01-03'
union all select 3,'2009-01-03','2009-01-08'
union all select 4,'2009-01-01','2009-01-03'
union all select 5,'2009-01-06','2009-01-08'
union all select 6,'2009-01-01','2009-01-01'
DECLARE @s VARCHAR(10),@e VARCHAR(10)
SELECT @s='2009-01-02',@e='2009-01-06'
SELECT * FROM @a WHERE @e>=sdd and @s<=edd
/*
id sdd edd
----------- ---------- ----------
1 2009-01-01 2009-01-31
2 2009-01-03 2009-01-03
3 2009-01-03 2009-01-08
4 2009-01-01 2009-01-03
5 2009-01-06 2009-01-08
(所影响的行数为 5 行)
*/
DECLARE @a TABLE(id INT,sdd varchar(10),edd VARCHAR(10))
insert @a select 1,'2009-01-01','2009-01-31'
union all select 2,'2009-01-03','2009-01-03'
union all select 3,'2009-01-03','2009-01-08'
union all select 4,'2009-01-01','2009-01-03'
union all select 5,'2009-01-06','2009-01-08'
union all select 6,'2009-01-01','2009-01-01'
DECLARE @s VARCHAR(10),@e VARCHAR(10)
SELECT @s='2009-01-02',@e='2009-01-06'
SELECT * FROM @a WHERE @e>=sdd and @s<edd
/*
id sdd edd
----------- ---------- ----------
1 2009-01-01 2009-01-31
2 2009-01-03 2009-01-03
3 2009-01-03 2009-01-08
4 2009-01-01 2009-01-03
5 2009-01-06 2009-01-08
(所影响的行数为 5 行)
*/
DECLARE @a TABLE(id INT,sdd varchar(10),edd VARCHAR(10))
insert @a select 1,'2009-01-01','2009-01-31'
union all select 2,'2009-01-03','2009-01-03'
union all select 3,'2009-01-03','2009-01-08'
union all select 4,'2009-01-01','2009-01-03'
union all select 5,'2009-01-06','2009-01-08'
union all select 6,'2009-01-01','2009-01-01'
DECLARE @s VARCHAR(10),@e VARCHAR(10)
SELECT @s='2009-01-02',@e='2009-01-06'
SELECT * FROM @a WHERE @e>sdd and @s<edd
/*
id sdd edd
----------- ---------- ----------
1 2009-01-01 2009-01-31
2 2009-01-03 2009-01-03
3 2009-01-03 2009-01-08
4 2009-01-01 2009-01-03
(所影响的行数为 4 行)
*/
DECLARE @a TABLE(id INT,sdd varchar(10),edd VARCHAR(10))
insert @a select 1,'2009-01-01','2009-01-31'
union all select 2,'2009-01-03','2009-01-03'
union all select 3,'2009-01-03','2009-01-08'
union all select 4,'2009-01-01','2009-01-03'
union all select 5,'2009-01-06','2009-01-08'
union all select 6,'2009-01-01','2009-01-01'
DECLARE @s VARCHAR(10),@e VARCHAR(10)
SELECT @s='2009-01-02',@e='2009-01-06'
SELECT * FROM @a WHERE not (@e<=sdd OR @s>=edd)
--result
/*id sdd edd
----------- ---------- ----------
1 2009-01-01 2009-01-31
2 2009-01-03 2009-01-03
3 2009-01-03 2009-01-08
4 2009-01-01 2009-01-03
(所影响的行数为 4 行)
*/