表A字段 MC T_S T_E
DD 2009-9-21 09:30:00 2009-9-21 10:30:00
CC 2009-9-21 10:00:00 2009-9-21 11:00:00
EE 2009-9-21 12:30:00 2009-9-21 13:30:00现在给出的时间段是 从2009-9-21 09:50:00至2009-9-21 10:50:00,
如何用SQL语句查询表A中有多少数据 在上述时间段中?不知道表述明确了吗?求教?
DD 2009-9-21 09:30:00 2009-9-21 10:30:00
CC 2009-9-21 10:00:00 2009-9-21 11:00:00
EE 2009-9-21 12:30:00 2009-9-21 13:30:00现在给出的时间段是 从2009-9-21 09:50:00至2009-9-21 10:50:00,
如何用SQL语句查询表A中有多少数据 在上述时间段中?不知道表述明确了吗?求教?
select * from tb where t_s>'2009-9-21 09:50:00' and t_e<'2009-9-21 10:50:00'
and t_s<'2009-9-21 10:50:00'
T_S BETWEEN '2009-9-21 09:50:00' AND '2009-9-21 10:50:00'
OR
T_E BETWEEN '2009-9-21 09:50:00' AND '2009-9-21 10:50:00'??
where T_S between '2009-9-21 09:50:00' and '2009-9-21 10:50:00'
or T_E between '2009-9-21 09:50:00' and '2009-9-21 10:50:00'
*
from
tb
where
T_S between '2009-9-21 09:50:00' and '2009-9-21 10:50:00'
or
T_E between '2009-9-21 09:50:00' and '2009-9-21 10:50:00'
FROM TB
WHERE T_S between '2009-9-21 09:50:00' and '2009-9-21 10:50:00'
OR T_E between '2009-9-21 09:50:00' and '2009-9-21 10:50:00'
select * from tb where datediff(minute,t_s,'2009-9-21 09:50:00')<0
and datediff(minute,t_e,'2009-9-21 10:50:00')>0
and t_s<='2009-9-21 10:50:00'
and t_e<='2009-9-21 10:50:00'
select count(1) as 记录数 from 表A WHERE (t_s between '2009-9-21 09:50:00'
and '2009-9-21 10:50:00')
and t_e<='2009-9-21 10:50:00'
between...and...是含=的,会有个临界点问题,可能要用>、<号
SELECT *
FROM TB
WHERE ( T_S > '2009-9-21 09:50:00' and T_S < '2009-9-21 10:50:00' )
OR ( T_E > '2009-9-21 09:50:00' and T_B < '2009-9-21 10:50:00' )
SELECT *
FROM TB
WHERE ( T_S > '2009-9-21 09:50:00' and T_S < '2009-9-21 10:50:00' )
OR ( T_B > '2009-9-21 09:50:00' and T_B < '2009-9-21 10:50:00' )
select * from A
where T_S between '2009-9-21 09:50:00' and '2009-9-21 10:50:00'
or T_E between '2009-9-21 09:50:00' and '2009-9-21 10:50:00'
insert into tb values('DD' , '2009-9-21 09:30:00' , '2009-9-21 10:30:00')
insert into tb values('CC' , '2009-9-21 10:00:00' , '2009-9-21 11:00:00' )
insert into tb values('EE' , '2009-9-21 12:30:00' , '2009-9-21 13:30:00' )declare @sdate datetime
declare @edate datetime
set @sdate = '2009-9-21 09:50:00'
set @edate = '2009-9-21 10:50:00'select distinct m.* from tb m ,
(
select fenzhong =
dateadd(mi,num,@sdate)
from
(select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a
where
dateadd(mi,num,@sdate)<=@edate
) n
where n.fenzhong between m.t_s and m.t_edrop table tb/*
MC T_S T_E
---------- ------------------------------------------------------ ------------------------------------------------------
CC 2009-09-21 10:00:00.000 2009-09-21 11:00:00.000
DD 2009-09-21 09:30:00.000 2009-09-21 10:30:00.000(所影响的行数为 2 行)
*/
select * from tb where t_s>'2009-9-21 09:50:00' and t_e<'2009-9-21 10:50:00'
不就行了吗