select * from tb where begindate>='2012-1-1' and enddate<='2012-12-31'
select * from tb where (begindate>='2012-1-1' and enddate<='2012-12-31') Or (begindate<='2012-1-1' and enddate>='2012-12-31') Or (begindate>'2012-1-1' and begindate<'2012-12-31') Or (enddate>'2012-1-1' and enddate<'2012-12-31')
select * from tb where (begindate>='2012-1-1' and enddate<='2012-12-31') or (begindate<'2012-1-1' and enddate>'2012-12-31')
如果时间短是连续的话,我记得这个就够了SELECT * FROM TB WHERE enddate >= '2012-1-1' AND startdate <= '2012-12-30'
两种情况都行: 1:数据库中记录的开始时间比给定的结束时间小 2:数据库中记录的结束时间比给定的开始时间大--racer if object_id('racer','u') is not null drop table racercreate table racer ( id int primary key, name nvarchar(20), team nvarchar(20), begindate date, enddate date ) go insert into racer values (1001,'name1','team1','2012-02-12','2012-07-09'), (1002,'name2','team2','2012-03-12','2012-08-09'), (1003,'name3','team3','2012-04-12','2012-09-09'), (1004,'name4','team4','2012-05-12','2012-10-09'), (1005,'name5','team5','2012-06-12','2012-11-09'), (1006,'name6','team6','2012-07-12','2012-12-09')go --SQL declare @date1 date declare @date2 dateset @date1='2012-04-23' set @date2='2012-11-23'select *From racer where begindate<@date2 or enddate>@date1--结果集 --1001 name1 team1 2012-02-12 2012-07-09 --1002 name2 team2 2012-03-12 2012-08-09 --1003 name3 team3 2012-04-12 2012-09-09 --1004 name4 team4 2012-05-12 2012-10-09 --1005 name5 team5 2012-06-12 2012-11-09 --1006 name6 team6 2012-07-12 2012-12-09
select * from tb where ENDdate>='2012-1-1' AND begindate<='2012-12-31'
说下这个的逻辑吧,这个逻辑好多知道答案了也不容易理清把时间分为三段 2012-1-1前,2012-1-1 到 2012-12-30,2012-12-30后现在需要begindate(起始时间) 到enddate(终止时间)的时间段与2012-1-1 到 2012-12-30有交集 其反命题就是 begindate(起始时间) 到enddate(终止时间)全部落在另外两段另外两段的写法分别是:ENDdate<'2012-1-1' 和 begindate>'2012-12-31' 那我们要的就是NOT (ENDdate<'2012-1-1' OR begindate>'2012-12-31') 逻辑变换下就是 ENDdate>='2012-1-1' AND begindate<='2012-12-31'
是四个时间段的交集,网站上查了资料是这样的:BeginDate BETWEEN '2012-1-1' AND '2012-12-30' OR EndDate BETWEEN '2012-1-1' AND '2012-12-30' OR '2012-1-1' BETWEEN BeginDate AND EndDate OR '2012-12-30' BETWEEN BeginDate AND EndDate大家觉得呢?
where (begindate>='2012-1-1' and enddate<='2012-12-31') or (begindate<'2012-1-1' and enddate>'2012-12-31')
FROM TB
WHERE enddate >= '2012-1-1'
AND startdate <= '2012-12-30'
1:数据库中记录的开始时间比给定的结束时间小
2:数据库中记录的结束时间比给定的开始时间大--racer
if object_id('racer','u') is not null
drop table racercreate table racer
(
id int primary key,
name nvarchar(20),
team nvarchar(20),
begindate date,
enddate date
)
go
insert into racer values
(1001,'name1','team1','2012-02-12','2012-07-09'),
(1002,'name2','team2','2012-03-12','2012-08-09'),
(1003,'name3','team3','2012-04-12','2012-09-09'),
(1004,'name4','team4','2012-05-12','2012-10-09'),
(1005,'name5','team5','2012-06-12','2012-11-09'),
(1006,'name6','team6','2012-07-12','2012-12-09')go
--SQL
declare @date1 date
declare @date2 dateset @date1='2012-04-23'
set @date2='2012-11-23'select *From racer
where begindate<@date2 or enddate>@date1--结果集
--1001 name1 team1 2012-02-12 2012-07-09
--1002 name2 team2 2012-03-12 2012-08-09
--1003 name3 team3 2012-04-12 2012-09-09
--1004 name4 team4 2012-05-12 2012-10-09
--1005 name5 team5 2012-06-12 2012-11-09
--1006 name6 team6 2012-07-12 2012-12-09
2012-1-1前,2012-1-1 到 2012-12-30,2012-12-30后现在需要begindate(起始时间) 到enddate(终止时间)的时间段与2012-1-1 到 2012-12-30有交集
其反命题就是 begindate(起始时间) 到enddate(终止时间)全部落在另外两段另外两段的写法分别是:ENDdate<'2012-1-1' 和 begindate>'2012-12-31'
那我们要的就是NOT (ENDdate<'2012-1-1' OR begindate>'2012-12-31')
逻辑变换下就是 ENDdate>='2012-1-1' AND begindate<='2012-12-31'
EndDate BETWEEN '2012-1-1' AND '2012-12-30' OR
'2012-1-1' BETWEEN BeginDate AND EndDate OR
'2012-12-30' BETWEEN BeginDate AND EndDate大家觉得呢?